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

score 102 · Accepted Answer · edited Dec 12 '18 at 18:36

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:

Including your source material is great. I also found that you first have to read an XML string from the DB into a variable before you can process it. There appears to be no way to `SELECT FROM` a subquery that returns XML. — cjbarth, Nov 14 '13 at 10:51
Apparently I spoke too soon. This post covers how to select XML without a variable: http://stackoverflow.com/a/18361423/271351. Basically you `CROSS APPLY` the table with the field in the table that has the XML and then pull the `NODES` from the `CROSS APPLY`ed field. — cjbarth, Nov 14 '13 at 13:35

score 30 · Answer 2 · edited Mar 03 '20 at 08:32

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

This works well. Even better is you can use an existing table to define the schema in the with. — Anthony Queen, Mar 24 '17 at 12:51
Here's how you can [upload a file directly](https://stackoverflow.com/a/11540442/5432315), instead of using a string (which isn't a super likely use case). — Brad Turek, Mar 02 '20 at 21:46

Convert Xml to Table SQL Server

2 Answers2

1

2.

Linked