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

asked 20 Apr '11, 19:24

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%

edited 26 Apr '11, 11:09

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109

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

(20 Apr '11, 21:37) Ron Hiner

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

permanent link

answered 20 Apr '11, 21:58

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

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

(20 Apr '11, 22:15) Ron Hiner
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 20 Apr '11, 19:24

question was seen: 1,764 times

last updated: 26 Apr '11, 11:09