Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Emulate XMLTABLE in SQL Server with NODES() and VALUE() #10099

Closed
lukaseder opened this issue Apr 22, 2020 · 2 comments
Closed

Emulate XMLTABLE in SQL Server with NODES() and VALUE() #10099

lukaseder opened this issue Apr 22, 2020 · 2 comments

Comments

@lukaseder
Copy link
Member

SQL Server supports OPENXML, which seems to differ only in syntax from standard SQL XML_TABLE:
https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-ver15

@lukaseder
Copy link
Member Author

OPENXML requires the document to be "prepared" and "removed" using:

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument 
SELECT * FROM OPENXML (@DocHandle, :path)
EXEC sp_xml_removedocument @DocHandle 

While it would be possible to generate such a statement batch, it might have unwanted side effects (like update counts).

It should be possible to emulate XML_TABLE with xml.nodes() instead

@lukaseder
Copy link
Member Author

This seems to work:

select 
  t2.x.value('(a/@value)[1]', 'varchar(20)') as a, 
  t2.x.value('(b/@value)[1]', 'int') as b
from 
  (select cast('<table><row><a value="X">Nope</a><b value="1">Nope</b></row><row><a value="Y">Nope</a><b value="2">Nope</b></row></table>' as xml)) t1(x)
  cross apply t1.x.nodes('/table/row') t2 (x);

@lukaseder lukaseder changed the title Emulate XML_TABLE in SQL Server with OPENXML Emulate XMLTABLE in SQL Server with OPENXML Apr 22, 2020
@lukaseder lukaseder changed the title Emulate XMLTABLE in SQL Server with OPENXML Emulate XMLTABLE in SQL Server with NODES() and VALUE() Apr 22, 2020
3.14 XML and JSON automation moved this from To do to Done Apr 22, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
Development

No branches or pull requests

1 participant