Question Re: Openxml() syntax problem in Technology Q&A

Openxml() syntax problem

Former Member — Wed, 20 Apr 2011 19:24:34 GMT

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

Re: Openxml() syntax problem

Former Member — Wed, 20 Apr 2011 21:37:21 GMT

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

Re: Openxml() syntax problem

Former Member — Wed, 20 Apr 2011 21:58:56 GMT

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

Re: Openxml() syntax problem

Former Member — Wed, 20 Apr 2011 22:15:06 GMT

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