Read XML document stored in SQL Server 2008 R2 with XML datatype

Question

I am using SQL Server 2008 R2 and ASP.NET 3.5. I am storing Data table (.NET) at SQL side in XML Form. Column has data type XML.

This is an example of the XML being stored in the SQL Server in a table column.

<container>
<NewDataSet>

<Containers>
  <BARCODE>680812</BARCODE>
  <CATEGORYID>114</CATEGORYID>
  <NAME>KS000147</NAME>
  <GWT>4.640</GWT>
  <NWT>4.640</NWT>
  <LAM>30.00</LAM>
  <QTY>2</QTY>
  <QUANTITY>1</QUANTITY>
  <REMARKS>HM</REMARKS>
</Containers>
<Containers>
  <BARCODE>680813</BARCODE>
  <CATEGORYID>114</CATEGORYID>
  <NAME>KS000147</NAME>
  <GWT>4.680</GWT>
  <NWT>4.680</NWT>
  <LAM>30.00</LAM>
  <QTY>2</QTY>
  <QUANTITY>1</QUANTITY>
  <REMARKS>HM</REMARKS>
</Containers>
<Containers>
  <BARCODE>680814</BARCODE>
  <CATEGORYID>114</CATEGORYID>
  <NAME>KS000147</NAME>
  <GWT>4.490</GWT>
  <NWT>4.490</NWT>
  <LAM>30.00</LAM>
  <QTY>2</QTY>
  <QUANTITY>1</QUANTITY>
  <REMARKS>HM</REMARKS>
</Containers>
</NewDataSet>
</container>

Now I want to fetch this XML in a tabluar form. How do I write the query in SQL to fetch XML in following tabular format? i.e.

BARCODE | CATEGORYID |NAME     |GWT   |NWT   |LAM   |QTY |QUANTITY |REMARKS 
680814  |    114     |KS000147 |4.490 |4.490 |30.00 | 2  |   1     |HM

Let me know if you need more detail.

Solution

You can try something like this:

SELECT
    Barcode = Container.value('(BARCODE)[1]', 'int'),
    CategoryID = Container.value('(CATEGORYID)[1]', 'int'),
    Name = Container.value('(NAME)[1]', 'varchar(50)'),
    GWT = Container.value('(GWT)[1]', 'decimal(10, 3)'),
    NWT = Container.value('(NWT)[1]', 'decimal(10, 3)'),
    LAM = Container.value('(LAM)[1]', 'decimal(10, 3)'),
    QTY = Container.value('(QTY)[1]', 'int'),
    Quantity = Container.value('(QUANTITY)[1]', 'int'),
    Remarks = Container.value('(REMARKS)[1]', 'varchar(50)')
FROM
    dbo.YourTableHere
CROSS APPLY 
    XmlColumnHere.nodes('/container/NewDataSet/Containers') AS T(Container)
WHERE
    ID = 1

This will produce an output something like this (for your given sample data):