Question Re: OPENXML with Array an Attribute in Technology Q&A

OPENXML with Array an Attribute

Baron — Wed, 15 Jul 2020 11:18:10 GMT

I am trying to retrieve the value of Client Element which is a row in array returned as XML SOAP.

I use the following call, but don't know why I dont get any result (and no any error too).

select * from openxml('

<soap-env:envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing">

<soap-env:body>

  <ns1:getdetailresponse xmlns:ns1="urn:tempuri:Ver1">

   <ns1:result xsi:nil="true"/>

   <mydataset xmlns="urn:tempuri:Ver1">

    <![CDATA[<?xml version="1.0"?>

     <dsclients xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

     <ttclient>

      <client>200403</client>

     </ttclient>

     </dsclients>]]>

   </mydataset>

  </ns1:getdetailresponse>

</soap-env:body>

</soap-env:envelope>',

'//*:Body/*:GetDetailResponse/*:MyDataset/*:DsClients/ttClient') with (CusotmerID varchar (100) 'Client');

Can anyone help please?

Re: OPENXML with Array an Attribute

VolkerBarth — Wed, 15 Jul 2020 12:04:00 GMT

I'm not sure you can query the contents of a CDATA section via XPath directly - you might first need to extract it as a separate XML document here...

Generally, when parsing "new XML documents" with OpenXML, I have learnt to use the meta properties within the WITH clause to make sure I'm querying the right nodes (and do not stumble over missing namespaces and the like, as I have done all too often)...

Re: OPENXML with Array an Attribute

Baron — Wed, 15 Jul 2020 14:13:18 GMT

do you mean that I should extract the content of CDATA as text and then extract each element separately? i.e. should I need to use OPENXML twice?

The problem is that I can't even extract the content of CDATA!

By replacing the last line in the code above for example with:

'//*:Body/*:GetDetailResponse/*:MyDataset') with (CusotmerID varchar (100) 'text()');

Re: OPENXML with Array an Attribute

Baron — Wed, 15 Jul 2020 15:58:12 GMT

My main problem is not with CDATA, as I can replace <![CDATA[<?xml version="1.0"?> with '' and ]]> with ''.

I am not sure where exactly to use *: and where not to use in the last line of the code

Re: OPENXML with Array an Attribute

VolkerBarth — Wed, 15 Jul 2020 16:08:40 GMT

You can always use '*:' before any node/attribute name to allow any namespace - unless the XML actually does use identical names in different name spaces so you have to distinguish the name spaces.

But note that names are case-sensitive, while your sample does treat them as case-insensitive.

Re: OPENXML with Array an Attribute

Baron — Thu, 16 Jul 2020 04:21:04 GMT

Ok, clear. One more question, what if some element has an embedded attribute in it, like:

<mydataset xmlns="urn:tempuri:Ver1">

Do I need also to add *: too or *: is really only for the namespace preceeding the element name (something like ns1: or ns2: or whatsever).

Re: OPENXML with Array an Attribute

VolkerBarth — Thu, 16 Jul 2020 05:21:49 GMT

As stated, I'd really recommend to use the metaproperties to try to list all nodes and attributes with their values - that's the easiest way IMHO to find out how to adapt the desired XPath queries.

That being said, your last question is a general XML question better asked in general XML forums...

Re: OPENXML with Array an Attribute

Baron — Tue, 21 Jul 2020 06:12:04 GMT

I could get the correct value of client after correcting the following points:

replace '<![CDATA[<?xml version="1.0"?>' with ''
replace ']]>' with ''
replace 'xmlns="urn:tempuri:Ver1"' with ''
correcting capital & small letters (case sensitive)

My question is, how can I deactivate the effect of CDATA, so that I get the Data within CDATA as Markup and not as literal text:

In other words, I want to get:

<client>200403</client>

instead of:

 & lt ; client & gt ; 200403 & lt ;/client & gt ;

Re: OPENXML with Array an Attribute

Baron — Tue, 21 Jul 2020 06:18:57 GMT

Actually I could also solve this problem by:

replace(mytext, '& lt ;' , '<');

but this takes too long time, and I wanted to know whether there is some ready function for this.

Re: OPENXML with Array an Attribute

VolkerBarth — Tue, 21 Jul 2020 08:51:27 GMT

See the html_decode() and html_encode() builtin functions, the first should do exactly what you are trying to.

Re: OPENXML with Array an Attribute

VolkerBarth — Tue, 21 Jul 2020 08:54:52 GMT

Instead of using replace to "flatten" the CDATA contents, you might take the according node's contents and handle it as a separate XML document (which it is, obviously).

Re: OPENXML with Array an Attribute

Baron — Wed, 22 Jul 2020 03:18:12 GMT

Thank you very much, yes html_decode() does what I want.

Re: OPENXML with Array an Attribute

Baron — Wed, 22 Jul 2020 03:18:57 GMT

Yes, this is what I want, but I can't bring these together.

Is there any simple example showing how to deal with CDATA?

Re: OPENXML with Array an Attribute

VolkerBarth — Fri, 24 Jul 2020 10:06:55 GMT

There are XPath functions to do so according to the internets, but I guess those are not supported by SQL Anywhere's subset of XPath functions.