CODEWITHSUNDEEP
sqlxquery
james31rock
james31rock

Reputation: 2705

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

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

Upvotes: 0

Views: 1258

Answers (1)

james31rock
james31rock

Reputation: 2705

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

Upvotes: 1

Related Questions