T-SQL XQuery nested namespace issue

Question

I just want an actualEndTime - is that so much to ask?

declare @x xml = '
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
    <bogus>1934</bogus>
    <getJobActionsResponse xmlns="urn:JobService">
      <getJobActionsReturn>
        <job xmlns:ns1="urn:JobService" xsi:type="ns1:SvcJob">
          <actions />
          <actualEndTime>
            <dateString>2019-05-15 19:46:54.207</dateString>
          </actualEndTime>
        </job>
      </getJobActionsReturn>
    </getJobActionsResponse>
  </soapenv:Body>
</soapenv:Envelope>'

It seems from posts like this that I should be able to do a query like this:

;with xmlnamespaces (N'http://schemas.xmlsoap.org/soap/envelope/' as ns0, 
                     N'urn:JobService' as ns2)
select @x.value('(/ns0:envelope/ns0:body/ns2:getJobActions/ns2:getJobActionsReturn/ns2:job/ns2:actualEndTime)[1]', 'nvarchar(max)')

I've tried various permutations of namespace prefixing, but everything I try returns null. I can't even get an upstream bogus value to return non-null:

;with xmlnamespaces (N'http://schemas.xmlsoap.org/soap/envelope/' as ns0)
select @x.value('(/ns0:envelope/ns0:body/ns0:bogus)[1]', 'nvarchar(max)')

I don't get namespaces (every web page explaining them seems to be a mile long). Sorry, please and thanks.

The answer by marc_s is great and the way you should walk (in terms of "Be as specific as possible!", but there is an easy-cheesy approach too: SELECT @x.value('(//*:actualEndTime/*:dateString/text())[1]','datetime') — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 11, 2019 at 17:39

marc_s · Accepted Answer · 2019-06-11 17:15:32Z

2

You're close, but you had these mistakes:

Tag names are case-sensitive - you need to use <ns0:Envelope> (not <ns0:envelope>) etc.
You misspelled the <ns2:getJobActionsResponse> as <ns2:getJobActions>
You didn't go "all the way down" to the <ns2:dateString> element

Try this :

declare @x xml = '
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
    <bogus>1934</bogus>
    <getJobActionsResponse xmlns="urn:JobService">
      <getJobActionsReturn>
        <job xmlns:ns1="urn:JobService" xsi:type="ns1:SvcJob">
          <actions />
          <actualEndTime>
            <dateString>2019-05-15 19:46:54.207</dateString>
          </actualEndTime>
        </job>
      </getJobActionsReturn>
    </getJobActionsResponse>
  </soapenv:Body>
</soapenv:Envelope>'

;with xmlnamespaces (N'http://schemas.xmlsoap.org/soap/envelope/' as ns0, 
                     N'urn:JobService' as ns2)
select @x.value('(/ns0:Envelope/ns0:Body/ns2:getJobActionsResponse/ns2:getJobActionsReturn/ns2:job/ns2:actualEndTime/ns2:dateString)[1]', 'nvarchar(max)')

answered Jun 11, 2019 at 17:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

bwperrin Over a year ago

Ooo! Awesome. Now, what is with the odd fact that if I omit "/ns2:dateString", it still works?

marc_s Over a year ago

@bwperrin: that's because the <ns2:actualEndTime> node really only has one single child node with a value - so asking for the <ns2:actualEndTime> will return the inner child node's value - but that's just a bit "shaky" - don't rely on something like that - be explicit and complete/accurate!

Collectives™ on Stack Overflow

T-SQL XQuery nested namespace issue

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related