How can I efficiently transform XML data into a SQL Server table using Transact-SQL?

Transforming XML Data into a SQL Server Table

For data management scenarios involving XML, converting it into a structured table format for analysis and processing can be necessary. This task can be accomplished effectively using Transact-SQL (TSQL) in SQL Server.

XML to Table Conversion

The process of converting XML data into a table involves three approaches:

1. FOR XML PATH: This approach uses the FOR XML PATH clause to extract elements within the XML document and create a rowset. The data is stored in a single column as a hierarchical structure.
2. OPENXML: With OPENXML, you can iterate over the XML document using the OPENXML function. This method offers more control over the structure of the generated table.
3. XQuery: Utilizing XQuery expressions in the XQuery() function enables you to query and transform XML data directly within SQL Server.

Example

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>

Applying the FOR XML PATH method, the following code converts this XML into a table:

DECLARE @xml XML = 
'<root>
    <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>
</root>';

SELECT
    IdInvernadero,
    IdProducto,
    IdCaracteristica1,
    IdCaracteristica2,
    Cantidad,
    Folio
FROM
    @xml.nodes('//row') AS tbl(row)
CROSS APPLY
    tbl.row.query('./@*') AS col(name, value);

The resulting table:

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

By leveraging TSQL, you can efficiently transform XML data into structured tables, enabling data analysis, manipulation, and reporting tasks.

The above is the detailed content of How can I efficiently transform XML data into a SQL Server table using Transact-SQL?. For more information, please follow other related articles on the PHP Chinese website!