Convert Xml to Table SQL Server
213,942
Solution 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:
http://kennyshu.blogspot.com/2007/12/convert-xml-file-to-table-in-sql-2005.html
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
Solution 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
Comments
-
Sanchitos almost 2 years
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
-
cjbarth over 10 yearsIncluding 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 over 10 yearsApparently I spoke too soon. This post covers how to select XML without a variable: stackoverflow.com/a/18361423/271351. Basically you
CROSS APPLY
the table with the field in the table that has the XML and then pull theNODES
from theCROSS APPLY
ed field. -
Anthony Queen about 7 yearsThis works well. Even better is you can use an existing table to define the schema in the with.
-
Brad Turek about 4 yearsHere's how you can upload a file directly, instead of using a string (which isn't a super likely use case).