XQUERY / XPATH versus nodes containing ":"-character

SQL Server XML

JFoushee


Hi, all.

I'm having problems using XPATH / XQUERY versus input containing ":" in the node names.

Example:

Code Snippet
<MyData:Output xmlns:MyData="www.myprovider.com">
<MyData:Provider>
<MyData:Property MyData:Name="FirstName">Joe</MyData:Property>
</MyData:Provider>
</MyData:Output>

T-SQL (breaks):

Code Snippet
SELECT XMLContents.query('/MyData:Output/MyData:Provider')
FROM dbo.Some_Table
__________________________________________________________
XQuery [dbo.Some_Table.XMLContents.query()]: The name "MyData" does not denote a namespace.

However, if I remove all the "MyData:" references from code and data, it works fine.

I don't have an option of getting the incoming data changed;
is there a way to escape the character to get this code to work



Top

Re: XQUERY / XPATH versus nodes containing ":"-character

Martin Honnen


MyData is a prefix which you need to declare in your query as follows:

Code Snippet

DECLARE @x xml;

SET @x = '<MyData:Output xmlns:MyData="www.myprovider.com">

<MyData:Provider>

<MyData:Property MyData:Name="FirstName">Joe</MyData:Property>

</MyData:Provider>

</MyData:Output>';

SELECT @x.query('

declare namespace MyData="www.myprovider.com";

/MyData:Output/MyData:Provider

');






Top

Re: XQUERY / XPATH versus nodes containing ":"-character

JFoushee

Thank you so much! That did it!



Top