CODEWITHSUNDEEP
sql-serverxmlt-sqlsoap
dbrosier
dbrosier

Reputation: 330

Retrieving an XML node value with TSQL?

What am I not getting here? I can't get any return except NULL...

DECLARE @xml xml
SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SOAP-ENV:Body>
    <webregdataResponse>
      <result>0</result>
      <regData />
      <errorFlag>99</errorFlag>
      <errorResult>Not Processed</errorResult>
    </webregdataResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'

DECLARE @nodeVal int
SELECT @nodeVal =  @xml.value('(errorFlag)[1]', 'int')
SELECT @nodeVal

Upvotes: 0

Views: 841

Answers (2)

har07
har07

Reputation: 89295

That's because errorFlag is not the root element of your XML document. You can either specify full path from root element to errorFlag, for example* :

SELECT @nodeVal =  @xml.value('(/*/*/*/errorFlag)[1]', 'int')

or you can use descendant-or-self axis (//) to get element by name regardless of it's location in the XML document, for example :

SELECT @nodeVal =  @xml.value('(//errorFlag)[1]', 'int')

*: I'm using * instead of actual element name just to simplify the expression. You can also use actual element names along with the namespaces, like demonstrated in the other answer.

Upvotes: 0

JGV
JGV

Reputation: 5187

Here is the solution:

DECLARE @xml xml
SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SOAP-ENV:Body>
    <webregdataResponse>
      <result>0</result>
      <regData />
      <errorFlag>99</errorFlag>
      <errorResult>Not Processed</errorResult>
    </webregdataResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'


declare @table table (data xml);
insert into @table values (@xml);

WITH xmlnamespaces (
'http://schemas.xmlsoap.org/soap/envelope/' as [soap])
SELECT Data.value('(/soap:Envelope/soap:Body/webregdataResponse/errorFlag)[1]','int') AS ErrorFlag
FROM @Table ;

Running the above SQL will return 99.

Snapshot of the result is given below,

Snapshot of the result

Upvotes: 1

Related Questions