无法解析 XML-解网

问：

我需要帮助将 XML 变量解析为如下表格式。我是解析的新手，在谷歌中查找，能够解析，，但无法解析具有，，AccountIdEmailProductNameProductTypeCustNameCreatedDatebillingCode

   DECLARE @xmlvalue XML = 
'<Request>
  <AccountId>16000</AccountId>
  <Email>[email protected]</Email>
  <Attributes>
  <Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">CustName</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">TestName</Value></Attribute>
  <Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">CreatedDate</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">3/26/2022</Value></Attribute>
  <Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">BillingCode</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">testbiling</Value></Attribute>
  </Attributes>
  <ProductName>TestProduct</ProductName>
  <ProductType>Recurring</ProductType>
</Request>'

预期输出：

AccountId  Email           CustName  CreatedDate  BillingCode  ProductName ProductType
1600       [email protected]  TestName   03/26/2022  testbiling  TestProduct  Recurring

下面的代码给了我我想要的东西。有没有简单的方法？

SELECT req.req_col.value('AccountId[1]','INT')       AS AccountId, 
       req.req_col.value('Email[1]','VARCHAR(100)')  AS Email,
       req.req_col.value('ProductName[1]','VARCHAR(100)')    AS ProductName,
       req.req_col.value('ProductType[1]','VARCHAR(100)')    AS ProductType,
       v.CustName,
       v.CreatedDate,
       v.BillingCode
FROM @xmlvalue.nodes('/Request')                                req(req_col)
CROSS APPLY  ( SELECT *
                FROM (SELECT req.req_col.value('Name[1]','VARCHAR(100)')     AS Name,
                           req.req_col.value('Value[1]','VARCHAR(100)')      AS Value
                      FROM @xmlvalue.nodes('/Request/Attributes/Attribute') req(req_col)
                     ) tab1
                PIVOT (
                MAX(Value) FOR Name IN ( CustName, CreatedDate, BillingCode )) Tab2
           ) v

SQL Server XML 分析

with xmlnamespaces (
  'http://testtarget.com/wsdl/myAPI' as anything
)
select
  request.value(N'(AccountId/text())[1]', N'nvarchar(50)') as AccountId,
  request.value(N'(Email/text())[1]', N'nvarchar(50)') as Email,
  request.value(N'(Attributes/Attribute[anything:Name/text()="CustName"]/anything:Value/text())[1]', N'nvarchar(50)') as CustName,
  request.value(N'(Attributes/Attribute[anything:Name/text()="CreatedDate"]/anything:Value/text())[1]', N'nvarchar(50)') as CreatedDate,
  request.value(N'(Attributes/Attribute[anything:Name/text()="BillingCode"]/anything:Value/text())[1]', N'nvarchar(50)') as BillingCode,
  request.value(N'(ProductName/text())[1]', N'nvarchar(50)') as ProductName,
  request.value(N'(ProductType/text())[1]', N'nvarchar(50)') as ProductType
from @xmlvalue.nodes('/Request') foo(request);

帐户 ID	电子邮件	客户名称	创建日期	BillingCode（计费代码）	产品名称	产品类型
16000	[电子邮件保护]	测试名称	3/26/2022	测试	测试产品	经常性

无法解析 XML

Not able to parse XML

评论

评论