XML Query with multiple attributes

Question

I have the following XML:

<LoSTResponseBodyType xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <LoSTResponseAdapter xmlns="urn:adapt:xml:ns:DataTypes:2.0">
       <findServiceResponse xmlns="urn:lost:params:xml:ns:lost1">
           <mapping sourceId="9999" expires="2015-08-24T14:27:58Z" lastUpdated="2015-06-17T21:36:43Z" source="mysourcename">
              <displayName xml:lang="en">mydisplayname</displayName>
              <service>myservice</service>
              <uri>myuri</uri>
              <serviceNumber>mynumber</serviceNumber>
           </mapping>
      </findServiceResponse>
    </LoSTResponseAdapter>
</LoSTResponseBodyType>

How do I retrieve the value for <uri>, ie myuri?

I have tried the following code

WITH xmlnamespaces ('urn:adapt:xml:ns:DataTypes:2.0' as datatype,
                    'urn:lost:params:xml:ns:lost1' as findtype)
SELECT EventBody.value('(/LoSTResponseBodyType/datatype:LoSTResponseAdapter/findtype:findServiceResponse/mapping/uri)[1]', 'varchar(max)') 
FROM mytable

I do get a value of NULL.

If i remove everything 'mapping/uri', i do get all values all concatenated together, ie displayname, service, uri, serviceNumber and it would look like this: mydisplaynamemyservicemyurimynumber

Have you tried adding the findtype prefix to mapping and uri in your path? Those are also in the same namespace as findServiceResponse — Daniel Haley
– Daniel Haley, Commented Dec 18, 2015 at 0:05
Thank you very much for the quick response, that's what was missing indeed ! Appreciate it — xQueryUser
– xQueryUser, Commented Dec 18, 2015 at 0:31
@xQueryUser: You should invite Daniel Haley to submit his comment as an answer so that you can accept it. — kjhughes
– kjhughes, Commented Dec 18, 2015 at 2:10
This isn't exactly standard XQuery. Is it Microsoft's SQL-integrated bastardization? — Charles Duffy
– Charles Duffy, Commented Dec 18, 2015 at 15:21

Daniel Haley · Accepted Answer · 2015-12-18 15:15:46Z

1

Try adding the findtype prefix to mapping and uri in your xpath.

Those elements are also in the same namespace as findServiceResponse.

answered Dec 18, 2015 at 15:15

Daniel Haley

53.1k7 gold badges75 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

XML Query with multiple attributes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related