I have a rather big and structured XML receipt which one I want to parse into a relational database. There are some equal structures on different levels, so it'd be very good to parse them using the same SQL statement. Like:
DECLARE @XMLPath varchar(127)
SET @XMLPath = 'atag/btag/item'
INSERT INTO XMLReadItems
T.c.value('productname', 'varchar(63)') AS InvoiceTarget,
T.c.value('unit', 'varchar(15)') AS Unit,
FROM @XMLItems ci CROSS APPLY XMLCol.nodes(*[local-name()=sql:variable("@XMLPath")]') T(c)
@XMLPath could be a string from a variable or even a field from a table (what about using
sql:column()?). But any of them I couldn't make work. I can only use a static string in
Best How To :
There is no way you can construct XQuery parameter dynamically as it is limited to literal string only. See what MSDN says about the parameter of
nodes() XML method :
Is a string literal, an XQuery expression. If the query expression constructs nodes, these constructed nodes are exposed in the resulting rowset. If the query expression results in an empty sequence, the rowset will be empty. If the query expression statically results in a sequence that contains atomic values instead of nodes, a static error is raised.
Forcing to pass SQL variable to
nodes() method would trigger error :
The argument 1 of the XML data type method "nodes" must be a string literal.
The trick you're trying to implement only works for matching element by name dynamically, not constructing the entire XPath dynamically. For example, the following should work fine to shred on
item elements :
SET @elementName = 'item'
FROM @XMLItems ci
CROSS APPLY XMLCol.nodes('//*[local-name()=sql:variable("@elementName")]') T(c)
In the end there is no workaround to this limitation as far as I can see, unless you want to go farther to construct the entire query dynamically (see: