dynamic xml deserialization in a stored procedure

Question

dynamic xml deserialization in a stored procedure

mcginn

SSCommitted

Points: 1592
More actions
October 29, 2009 at 1:16 pm

#385677

I am passing data to a store procedure in an XML string. I am inserting into tables with insert into .... select ... from @myXML.nodes() as X(nref) syntax. I want the value defined for nodes to be dynamic.
I have:
insert into table1 (aa, bb)
select nref.value(...),
nref.value(...)
from @myXML.nodes('//root/dataDef1') as X(nref)
insert into table1 (aa, bb)
select nref.value(...),
nref.value(...)
from @myXML.nodes('//root/dataDef2') as X(nref)
With the literal string in the nodes() clause I need to either have 2 statements in a stored procedure or 1 statement in 2 procedures. I want
to make it dynamic such as
set @message = '''' + '//root/'+@var+''''
where @var is passed to the procedure with a value of either dataDef1 or dataDef2. The from clause would then be
from @myXML.nodes(@message) as X(nref)
But I get the message:
Msg 8172....
The argument 1 of the xml data type method "nodes" must be a string literal.
Any advice on how to resolve this error condition?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

It's not possible to use variables within XML expressions. If your node names are like "dataDef1" to "dataDefN" you can try to use the XPath functions name() and substring(). SQL Server 2005 and 2008 does not support the complete XPath 2.0 standard but this functions might be available. (I don't have a test environment at the moment to try it at the moment.)

If this doesn't work you have to move your whole statement to a dynamic approach.

Greets

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 2

You could always use dynamic SQL I suppose, or an edge table generated from OPENXML, or code a custom CLR solution. Depends on exactly what is required.

One example which might be of interest:

declare @x xml;

set @x = '<root><thing id="1" b="2" c="fish" /><thing id="2" b= "4" c="chicken" /></root>';

select v.value('@id', 'int'),

v.value('@b', 'int'),

v.value('@c', 'varchar(10)')

from @x.nodes('./root/thing[@id="1"]') n (v);

select v.value('@id', 'int'),

v.value('@b', 'int'),

v.value('@c', 'varchar(10)')

from @x.nodes('./root/thing[@id="2"]') n (v);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi Paul!

Nice solution, but mcginn's XML seems to work without attributes. Sadly, SQL Server does not support the name() function (just tried).

@mcginn:

If it is an option to change the input XML, Paul's solution should be the best solution - using native SQL Server features. If you cannot change the XML structure, think about Paul's second suggestion to use a CLR procedure or a SSIS job like this:

string xml = "<root><thing1 b=\"2\" c=\"fish\" /><thing2 b= \"4\" c=\"chicken\" /></root>";

XmlDocument dom = new XmlDocument();

dom.LoadXml(xml);

XmlNodeList nodes = dom.SelectNodes("./root/* [substring(name(), 1, 5)=\"thing\"]");

foreach (XmlElement node in nodes)

{

// .. process thing

}

Greets

Flo

mcginn SSCommitted Points: 1592 More actions · Answer 4

Does anyone have an example of the syntax for the node() type in dynamic SQL? I have tried numerous variations of the syntax and in each case execution fails.