XML Shredding to include attribute and inner element value

2
votes

Consider the following XML fragment: 

<Orders>
  <Totals>
     <Total Type="Merchandise">56.89</Total>
     <Total Type="Shipping">10.75</Total>
     <Total Type="Tax">0.00</Total>
     <Total Type="Order">67.64</Total>
     <Total Type="Discount">0.00</Total>
     <Total Type="ServiceFee">0.00</Total>
     <Total Type="ServiceFeeDiscount">0.00</Total>
  </Totals>  
</Orders>

I'd like to (using SQL's built in XQuery/XPath support), shred the Type attribute and the value of each Total into its own column and the inner value of each element into its own column, as seen below:

    Type           Amount  
Merchandise        56.89  
  Shipping         10.75  
     Tax            0.00

I've used all of the XQuery and XPath I know, to no avail. Any assistance would be greatly appreciated!

1
sqlxmlxml-parsingxqueryshred

1 Answers

1
votes

Wow, don't know why I couldn't figure this out before. See code below:

select T.data.value('@Type', 'varchar(20)'),
   T.data.value('.', 'varchar(20)')
from @data.nodes('Orders/Totals/Total') T(data)