November 25, 2014 at 2:14 pm
I have a XML in Source Table. I need to parse this XML to 3 different tables which has Parent Child relationship. I can do this in C# but currently for this i need to implement it at SQL server side.
The sample xml looks like:
<ROWSET>
<ROW>
<HEADER_ID>5001507</HEADER_ID>
<ORDER_NUMBER>42678548</ORDER_NUMBER>
<CUST_PO_NUMBER>LSWQWE1</CUST_PO_NUMBER>
<CUSTOMER_NUMBER>38087</CUSTOMER_NUMBER>
<CUSTOMER_NAME>UNIVERSE SELLER</CUSTOMER_NAME>
<LINE>
<LINE_ROW>
<HEADER_ID>5001507</HEADER_ID>
<LINE_ID>12532839</LINE_ID>
<LINE_NUMBER>1</LINE_NUMBER>
<ITEM_NUMBER>STAGEPAS 600I-CA</ITEM_NUMBER>
<ORDER_QUANTITY>5</ORDER_QUANTITY>
</LINE_ROW>
<LINE_ROW>
<HEADER_ID>5001507</HEADER_ID>
<LINE_ID>12532901</LINE_ID>
<LINE_NUMBER>3</LINE_NUMBER>
<ITEM_NUMBER>CD-C600 RK</ITEM_NUMBER>
<ORDER_QUANTITY>6</ORDER_QUANTITY>
</LINE_ROW>
<LINE_ROW>
<HEADER_ID>5001507</HEADER_ID>
<LINE_ID>12532902</LINE_ID>
<LINE_NUMBER>4</LINE_NUMBER>
<ITEM_NUMBER>CD-S300 RK</ITEM_NUMBER>
<ORDER_QUANTITY>8</ORDER_QUANTITY>
</LINE_ROW>
</LINE>
<PRCADJ>
<PRCADJ_ROW>
<PRICE_ADJUSTMENT_ID>43095064</PRICE_ADJUSTMENT_ID>
<HEADER_ID>5001507</HEADER_ID>
<LINE_ID>12532839</LINE_ID>
<ADJUSTED_AMOUNT>-126</ADJUSTED_AMOUNT>
</PRCADJ_ROW>
<PRCADJ_ROW>
<PRICE_ADJUSTMENT_ID>43095068</PRICE_ADJUSTMENT_ID>
<HEADER_ID>5001507</HEADER_ID>
<LINE_ID>12532840</LINE_ID>
<ADJUSTED_AMOUNT>-96.6</ADJUSTED_AMOUNT>
</PRCADJ_ROW>
</PRCADJ>
</ROW>
</ROWSET>
The issue is the Parent can have multiple child and each child can multiple sub child. How can i write query to transfer this into Sql Server 2005
November 25, 2014 at 11:15 pm
Quick suggestion, have a look at this post which tackles similar problem
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply