Need to use SQL variable to define namespace for XML.Value selection

Question

I know how to select a value from an XML field using xpath and defining namespaces, but I need to use several xpath queries and assign them to my selection. Is there an easier way than doing the following:

SELECT 
    id, name,
    [XML].value('declare namespace test="http://www.test.org/xml/";
                 declare namespace test2="http://www.test2.org";
                 (//test:Address[1][test2:Global=1]/test:Street)[1] ', 'varchar(max)') AS streetLocation1,
    [XML].value('declare namespace test="http://www.test.org/xml/";
                 declare namespace test2="http://www.test2.org";
                 (//test:Address[2][test2:Global=1]/test:Street)[1] ', 'varchar(max)') AS streetLocation2 
FROM  
    TEST

I want to replace

'declare namespace test="http://www.test.org/xml/";
 declare namespace test2="http://www.test2.org";'

by using a variable. I tried to append strings but I got the following:

The argument 1 of the XML data type method "value" must be a string literal.

There has to be an easier way.

Thanks,

-James

Have a look at with xmlnamespaces. You can't use variables in there but you only have to declare the namespaces once for the entire query. — Mikael Eriksson
– Mikael Eriksson, Commented Sep 24, 2012 at 20:50

james31rock · Accepted Answer · 2012-09-25 12:47:29Z

1

Thanks @MikaelEriksson,

In case anyone has a similar issues. Here is the answer.

;WITH XMLNAMESPACES ('http://www.test.org/xml/' as test, 'http://www.test.org/xml/' as test2)
SELECT id,
       name,
       [XML].value('(//test:Address[1][test2:Global=1]/test:Street)[1] ', 'varchar(max)') AS streetLocation1,
       [XML].value('(//test:Address[2][test2:Global=1]/test:Street)[1] ', 'varchar(max)') AS streetLocation2 
FROM  TEST

answered Sep 25, 2012 at 12:47

james31rock

2,7152 gold badges22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need to use SQL variable to define namespace for XML.Value selection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related