How to Convert XML Data to a Table in SQL Server?

Convert XML to Table in SQL Server

In SQL Server, converting XML data to a tabular format can be achieved through various methods. One approach involves using the nodes method to extract the desired elements from the XML structure and then selecting those elements as columns in the resulting table.

Consider the following XML data:

<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 convert this XML into a table, follow these steps:

1. Create a table to store the data:

CREATE TABLE TableName (
    IdInvernadero smallint,
    IdProducto smallint,
    IdCaracteristica1 smallint,
    IdCaracteristica2 smallint,
    Cantidad int,
    Folio varchar(7)
);

2. Load the XML into a variable:

DECLARE @xml XML = '<root><row><IdInvernadero>...</row><row><IdInvernadero>...</row></root>';

3. Extract the elements using the nodes method:

INSERT INTO TableName (
    IdInvernadero,
    IdProducto,
    IdCaracteristica1,
    IdCaracteristica2,
    Cantidad,
    Folio
)
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);

This query will insert the extracted elements into the table with the specified column names. The resulting table will have the following structure:

IdInvernadero	IdProducto	IdCaracteristica1	IdCaracteristica2	Cantidad	Folio
8	3	8	8	25	4568457
3	3	1	2	72	4568457

The above is the detailed content of How to Convert XML Data to a Table in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!