Help with XML nodes function 1

[ddiamond]

The following code snippet works:

DECLARE @Response XML

SET @Response = 
'<soap:Envelope xmlns:soap="[URL unfurl="true"]http://schemas.xmlsoap.org/soap/envelope/"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema">[/URL]
  <soap:Body>
    <ProcessUWResponse>
      <OverallResult>Failure</OverallResult>
      <Result>Failure</Result>
    </ProcessUWResponse>
  </soap:Body>
</soap:Envelope>'

SELECT
  row.value('(//OverallResult)[1]', 'VARCHAR(20)') as Result1,
  row.value('(//Result)[1]', 'VARCHAR(20)') as Result2
FROM @Response.nodes('.') AS T(row)

Result1  Result2
-------  -------
Failure  Failure

The following code does not work:

DECLARE @Response XML

SET @Response = 
'<soap:Envelope xmlns:soap="[URL unfurl="true"]http://schemas.xmlsoap.org/soap/envelope/"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema">[/URL]
  <soap:Body>
    <ProcessUWResponse xmlns="[URL unfurl="true"]http://rebusis.com/webservices/gcs/IntegrationService">[/URL]
      <OverallResult>Failure</OverallResult>
      <Result>Failure</Result>
    </ProcessUWResponse>
  </soap:Body>
</soap:Envelope>'

SELECT
  row.value('(//OverallResult)[1]', 'VARCHAR(20)') as Result1,
  row.value('(//Result)[1]', 'VARCHAR(20)') as Result2
FROM @Response.nodes('.') AS T(row)

Result1  Result2
-------  -------
Null     Null

The only difference is the attribute xmlns="

http://rebusis.com/webservices/gcs/IntegrationService".

Why is this attribute causing the X-Path expressions to fail?

 

[kss444]

Not sure I can help, but try

http://rebusis.com/webservices/gcs/IntegrationService/

put an ending "/"

Ordinary Programmer

 

[gmmastros]

try:

DECLARE @Response XML

SET @Response =
'<soap:Envelope xmlns:soap="[URL unfurl="true"]http://schemas.xmlsoap.org/soap/envelope/"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema">[/URL]
  <soap:Body>
    <ProcessUWResponse xmlns="[URL unfurl="true"]http://rebusis.com/webservices/gcs/IntegrationService">[/URL]
      <OverallResult>Failure</OverallResult>
      <Result>Failure</Result>
    </ProcessUWResponse>
  </soap:Body>
</soap:Envelope>'

SELECT
  row.value('[!]declare default element namespace "[URL unfurl="true"]http://rebusis.com/webservices/gcs/IntegrationService";[/URL][/!](//OverallResult)[1]', 'VARCHAR(20)') as Result1,
  row.value('[!]declare default element namespace "[URL unfurl="true"]http://rebusis.com/webservices/gcs/IntegrationService";[/URL][/!](//Result)[1]', 'VARCHAR(20)') as Result2
FROM @Response.nodes('.') AS T(row)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

By the way.... nice question. The way you wrote the question and then code made it easy for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

George,

You da'man. Your solution worked great!

- Dan