将Xml转换为表SQL服务器

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

我想知道如何读取xml数据并将其转换为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

4 个解决方案

#1

This is the answer, hope it helps someone :)

这就是答案，希望它能帮助某人:

First they are two variations on how the xml can be wrote:

首先，它们是如何编写xml的两个变体:

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

   http://kennyshu.blogspot.com/2007/12/convert - xml -文件-表- - sql - 2005. - html

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

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

#2

Use sp_xml_preparedocument.

使用sp_xml_preparedocument。

For detail check: http://technet.microsoft.com/en-gb/library/ms186918.aspx

对细节检查:http://technet.microsoft.com/en-gb/library/ms186918.aspx

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 

#3

If you have deeply nested XML documents (or json, html, sql) with recursive nodes (node 'folder' in node 'folder' in node 'folder') of a mixed complex type without an XSD/DTD schema you can use the tool eXtractorONE (eXtractor.ONE). No programming needed, no Xquery, no XSLT, nearly zero configuration. No limit on size. Just point to the folder with XML documents, select your target database and run it.

如果您拥有混合复杂类型的深度嵌套XML文档(或json、html、sql)和递归节点(节点'文件夹'中的节点'文件夹'在节点'文件夹'中的节点'文件夹')，而没有XSD/DTD模式，您可以使用工具eXtractorONE (eXtractorONE . one)。不需要编程，不需要Xquery，不需要XSLT，几乎不需要配置。没有限制大小。只需指向包含XML文档的文件夹，选择目标数据库并运行它。

#4

To accomplish such conversion task you may consider to use some XML mapping converting tool like XMLFox Advance. We have been using the XMLFox Advance converter for years to transform consumers XML data to SQL server tables.

为了完成这种转换任务，您可以考虑使用一些XML映射转换工具，如XMLFox Advance。多年来，我们一直使用XMLFox高级转换器将使用者XML数据转换为SQL服务器表。