The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.6k373110

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:

×13

question asked: 20 Apr '11, 19:24

question was seen: 1,829 times

last updated: 26 Apr '11, 11:09