XML : XML Query with multiple attributes

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

No comments:

Post a Comment