Convert Xml to Table SQL Server

Question

I wonder how can i read a xml data and transform it to a table in TSQL?

For example:

<row>     <IdInvernadero>8</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>8</IdCaracteristica1>     <IdCaracteristica2>8</IdCaracteristica2>     <Cantidad>25</Cantidad>     <Folio>4568457</Folio> </row> <row>     <IdInvernadero>3</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>1</IdCaracteristica1>     <IdCaracteristica2>2</IdCaracteristica2>     <Cantidad>72</Cantidad>     <Folio>4568457</Folio> </row>       

To

8   3   8   8   25  4568457 3   3   1   2   72  4568457 

Answer 1

This is the answer, hope it helps someone :)

First there are two variations on how the xml can be written:

1

<row>     <IdInvernadero>8</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>8</IdCaracteristica1>     <IdCaracteristica2>8</IdCaracteristica2>     <Cantidad>25</Cantidad>     <Folio>4568457</Folio> </row> <row>     <IdInvernadero>3</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>1</IdCaracteristica1>     <IdCaracteristica2>2</IdCaracteristica2>     <Cantidad>72</Cantidad>     <Folio>4568457</Folio> </row> 

Answer:

SELECT          Tbl.Col.value('IdInvernadero[1]', 'smallint'),          Tbl.Col.value('IdProducto[1]', 'smallint'),          Tbl.Col.value('IdCaracteristica1[1]', 'smallint'),        Tbl.Col.value('IdCaracteristica2[1]', 'smallint'),        Tbl.Col.value('Cantidad[1]', 'int'),        Tbl.Col.value('Folio[1]', 'varchar(7)') FROM   @xml.nodes('//row') Tbl(Col)   

2.

<row IdInvernadero="8" IdProducto="3" IdCaracteristica1="8" IdCaracteristica2="8" Cantidad ="25" Folio="4568457" />                          <row IdInvernadero="3" IdProducto="3" IdCaracteristica1="1" IdCaracteristica2="2" Cantidad ="72" Folio="4568457" /> 

Answer:

SELECT          Tbl.Col.value('@IdInvernadero', 'smallint'),          Tbl.Col.value('@IdProducto', 'smallint'),          Tbl.Col.value('@IdCaracteristica1', 'smallint'),        Tbl.Col.value('@IdCaracteristica2', 'smallint'),        Tbl.Col.value('@Cantidad', 'int'),        Tbl.Col.value('@Folio', 'varchar(7)')  FROM   @xml.nodes('//row') Tbl(Col) 

Taken from:

1. http://kennyshu.blogspot.com/2007/12/convert-xml-file-to-table-in-sql-2005.html

2. http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

Answer 2

The sp_xml_preparedocument stored procedure will parse the XML and the OPENXML rowset provider will show you a relational view of the XML data.

For details and more examples check the OPENXML documentation.

As for your question,

DECLARE @XML XML SET @XML = '<rows><row>     <IdInvernadero>8</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>8</IdCaracteristica1>     <IdCaracteristica2>8</IdCaracteristica2>     <Cantidad>25</Cantidad>     <Folio>4568457</Folio> </row> <row>     <IdInvernadero>3</IdInvernadero>     <IdProducto>3</IdProducto>     <IdCaracteristica1>1</IdCaracteristica1>     <IdCaracteristica2>2</IdCaracteristica2>     <Cantidad>72</Cantidad>     <Folio>4568457</Folio> </row></rows>'  DECLARE @handle INT   DECLARE @PrepareXmlStatus INT    EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML    SELECT  * FROM    OPENXML(@handle, '/rows/row', 2)       WITH (     IdInvernadero INT,     IdProducto INT,     IdCaracteristica1 INT,     IdCaracteristica2 INT,     Cantidad INT,     Folio INT     )     EXEC sp_xml_removedocument @handle