Read XML document stored in SQL Server 2008 R2 wit

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.

Answer 1

The XML document in this example is contain of <customer>, <order>, and <orderdetail> elements.

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar>
<Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
<OrderDetail ProductID="16" Quantity="11" Amount="200$">
One of the best customer
</OrderDetail>
<OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
</Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
<Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
<OrderDetail ProductID="12" Quantity="9" Amount="180$">
Customer was very satisfied
</OrderDetail>
<OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
</Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML

--Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

The SELECT statement is used to retrieves all the columns in the rowset provided by OPENXML.

For view more Click here

Answer 2

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