Skip to content

Instantly share code, notes, and snippets.

@brunotdantas
Created December 3, 2018 13:34
Show Gist options
  • Save brunotdantas/97552cf05ec2467330a2d611167332e0 to your computer and use it in GitHub Desktop.
Save brunotdantas/97552cf05ec2467330a2d611167332e0 to your computer and use it in GitHub Desktop.
Example of reading a XML using OPENXML in SQL SERVER
DECLARE @XML XML
SET @XML = '<rows><row>
<transacao>
<IdInvernadero>8</IdInvernadero>
<IdProducto>3</IdProducto>
<cars>
<id>z</id>
<test> oi</test>
</cars>
<IdCaracteristica1>8</IdCaracteristica1>
<IdCaracteristica2>8</IdCaracteristica2>
<Cantidad>25</Cantidad>
<Folio>4568457</Folio>
</transacao>
</row>
<row>
<transacao>
<IdInvernadero>3</IdInvernadero>
<IdProducto>3</IdProducto>
<cars>
<id>bruno</id>
<test>oi</test>
</cars>
<IdCaracteristica1>1</IdCaracteristica1>
<IdCaracteristica2>2</IdCaracteristica2>
<Cantidad>72</Cantidad>
<Folio>45</Folio>
</transacao>
</row></rows>'
DECLARE @handle INT
DECLARE @PrepareXmlStatus INT
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML
SELECT *
FROM OPENXML(@handle, '/rows/row/transacao', 2)
WITH (
IdInvernadero varchar(max),
IdProducto INT,
IdCaracteristica1 INT,
IdCaracteristica2 INT,
Cantidad INT,
Folio INT 'Folio',
id varchar(max) 'cars/id'
)
EXEC sp_xml_removedocument @handle
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment