CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
ke3pup
ke3pup

Reputation: 1895

TSQL - Dynamic query to retrieve the value of given xml element using provided XPATH

I'm trying to write a SP in SQL Server 2008 where if the input is a valid XML and a XPATH i want to retrieve the value of that XPATH:

For example if i have:

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
                <checkRetrieve>
                    <party>
                        <user>
                            <first>JOHN</first>
                            <last>SMITH</last>
                        </user>
                        <media />
                    </party>
                </checkRetrieve>
            </genRetrieve>
        </soapenv:Body>
    </soapenv:Envelope>

And the XPATH is :

    /*[local-name() = "Envelope"][1]/*[local-name() = "Body"][1]/genRetrieve[1]/checkRetrieve[1]/party[1]/user[1]/first[1]

I need to grab "JOHN" and return it.

I tried the below 

    DECLARE @xml xml,
            @xpath nvarchar(max),
            @output nvarchar(100),
            @query nvarchar(max)

    SET @xml = @userINPUT
    SET xpath = '/*[local-name() = "Envelope"][1]/*[local-name() = "Body"][1]/genRetrieve[1]/checkRetrieve[1]/party[1]/user[1]/first[1]' +  '/text()'
    SET @output = ''    
    select @output = @xml.query('sql:variable("@xpath")')

But instead of the value i just get the full XPATH string i passed as argument in return. I also tried dynamic query:

    SET @query = 'SELECT @xml.query(N''' + @xpath + '/text()'')'
    print @query
    EXECUTE(@query)

But it gives "Must declare the scalar variable "@xml" erorr

Upvotes: 1

Views: 1775

Answers (1)

StuartLC
StuartLC

Reputation: 107267

The xmlns xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx" is a bit of a red herring - although it is declared, no nodes are actually defined in it. (Possibly unintended?)

You can use namespaces with XQuery with WITH NAMESPACES, which will allow you to avoid using the agnostic local-name():

WITH XMLNAMESPACES(
'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv,
'http://xxxxxxxxxxxxxxxxxxxxx' AS v1)
SELECT
  Nodes.node.value('(first)[1]', 'varchar(50)') AS FirstName,
  Nodes.node.value('(last)[1]', 'varchar(50)') AS LastName
FROM
  @xml.nodes('/soapenv:Envelope/soapenv:Body/genRetrieve/checkRetrieve/party/user')
    AS Nodes(node);

SqlFiddle here

Edit Apologies, I missed the point you wanted to do this dynamically:

DECLARE @query NVARCHAR(MAX) =
N'WITH XMLNAMESPACES(
''http://schemas.xmlsoap.org/soap/envelope/'' AS soapenv,
''http://xxxxxxxxxxxxxxxxxxxxx'' AS v1)
SELECT
  Nodes.node.value(''(' + @xpath + ')[1]'', ''varchar(50)'') AS FirstName
FROM
  @xml.nodes(''.'') AS Nodes(node)';    
exec sp_executesql @query, N'@xml xml', @xml = @xml;

Updated fiddle

Upvotes: 1

Related Questions