Openxml() syntax problem

[Former Member]

The lightbulb just isn't turning on in my head... I don't get it. (Ever have that feeling??)

Im trying to dig the value of my 'token' out of a mess of XML.... I read the docs, but I just don't follow it, so I just made sure I had the right number of parameters, and copied from code that works.

Yet my code doesn't. It appears to return an empty string, rather than my coveted token value.

:::SQL
select * from openxml(

-- xml string
'<?xml version="1.0" encoding="UTF-8"?><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>
<ns1:doAuthenticateResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"  xmlns:ns1="http://ws.defgh.com">
<doAuthenticateReturn xsi:type="ns2:SecurityToken" xmlns:ns2="urn:security.ws.abcd.com">
<token xsi:type="xsd:string">8ec45270-67fb-4e08-a0ca-8dee4e9c203c</token>
</doAuthenticateReturn></ns1:doAuthenticateResponse>
</soapenv:Body>
</soapenv:Envelope>',

-- xpath query & flag to map attributes to columns
'/token', 1,

-- list of namespaces in the XML string
'<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" />'
)
with ( mytoken char(35) 'token' )

BTW... I'm on 9.0.2

[Former Member]

Two things I discovered... I misdiagnosed above...I'm not getting an empty string -- instead I'm get no result at all.

And the other thing is that if I change the xpath query parameter to simply '/' -- I do get a result record, but it's got a NULL value in mytoken

[Former Member · Accepted solution]

I think you want something like the following:

:::SQL
select *
from openxml(

-- xml string
'<?xml version="1.0" encoding="UTF-8"?><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>
<ns1:doAuthenticateResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"  xmlns:ns1="http://ws.defgh.com">
<doAuthenticateReturn xsi:type="ns2:SecurityToken" xmlns:ns2="urn:security.ws.abcd.com">
<token xsi:type="xsd:string">8ec45270-67fb-4e08-a0ca-8dee4e9c203c</token>
</doAuthenticateReturn></ns1:doAuthenticateResponse>
</soapenv:Body>
</soapenv:Envelope>',

-- xpath query & flag to map attributes to columns
'/soapenv:Envelope/soapenv:Body/ns1:doAuthenticateResponse/doAuthenticateReturn/token', 1,

-- list of namespaces in the XML string
'<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" 
xmlns:ns1="http://ws.defgh.com"
/>'
)
with ( mytoken char(36) '.' )

You should use '.' to get the contents of the "token" element, you should use the full expected path to the token tag or perhaps '//token' to find a token tag anywhere with any parents. You might want to consider using *:foo for namespace wildcards if that is sufficient. If you do declare namespaces you might consider declaring the prefix mp bound to urn:ianywhere-com:sa-xpath-metaprop so you can access meta properties like @mp:id

[Former Member · Accepted solution]

Awesome Ivan! Not only did your changes work perfectly, I understand a bit better... thanks to your explanations.