I have xml data stored in one column of my table. Column datatype is nvarchar(max).
I need to query data from the column to extract some nodes information. Below is the example xml data stored
<Response>
<Quote>
<ID>1</ID>
<Item>
<ItemNumber>123456</ItemNumber>
</Item>
<Price>
<LPrice>12</LPrice>
</Price>
<Stocks>
<Stock>
<Quantity>2</Quantity>
</Stock>
<Stock>
<Quantity>1</Quantity>
</Stock>
</Stocks>
</Quote>
<Quote>
<ID>2</ID>
<Item>
<ItemNumber>234121</ItemNumber>
</Item>
<Price>
<ListPrice>34</ListPrice>
</Price>
<Stocks>
<Stock>
<Quantity>4</Quantity>
</Stock>
<Stock>
<Quantity>2</Quantity>
</Stock>
</Stocks>
</Quote>
</Response>
I want to get the information in the below format:
Item Number | LPrice | Quantity |
---|---|---|
123456 | 12 | 3 |
234121 | 34 | 6 |
Please help.