I need help parsing an XML variable into a table format as below. I am new to parsing looked up in google and was able to parse AccountId, Email, ProductName and ProductType but not able to parse nodes with in attribute like CustName, CreatedDate, billingCode

   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>'

Expected OUTPUT :

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

Below code is giving me what I wanted. Is there an easy approach?

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

CodePudding user response：

It is not necessary to use PIVOT for this, you can use filters in XPath expressions to select the various Attribute Name-Value element pairs, e.g.:

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);