Skip to main content

How to read / parse nested XML in CData Section / Base64 / encoded text using XML ODBC Driver

ZappySys Support Team
Updated

Problem

In this KB we will show you some hidden feature of XML ODBC Driver. If you have XML Data from File or REST API which has below sample patterns (XML inside XML CData Section OR Base64 encoded data) then you have to use ZappySys URL JOIN Feature (See more examples here) 

Sample Data

Here is some sample data from URL or use save sample XML as local file

Pattern #1 (CData Section - XML as Raw Text)

Notice in below XML Result Node contains cdata section which has XML you need to parse. When you have text like below your output will show data in column named "#cdata-section"

Sample Files:

https://zappysys.com/downloads/files/test/nested-cdata.xml

https://zappysys.com/downloads/files/test/nested-base64.xml

https://zappysys.com/downloads/files/test/nested-encoded.xml

https://zappysys.com/downloads/files/test/nested-encoded-no-attr.xml

Example XML with CData:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
 <Result>
 <![CDATA[<?xml version="1.0" encoding="utf-8"?>
<Data> 
 <Row><Id>111</Id><Name>AAA</Name></Row>
 <Row><Id>222</Id><Name>DDD</Name></Row>
 <Row><Id>333</Id><Name>CCC</Name></Row>
</Data>
 ]]>
 </Result>
 </soap:Body>
</soap:Envelope>

Example XML with Base64:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
  <Result>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4gPERhdGE+IDxSb3c+PElkPjExMTwvSWQ+PE5hbWU+QUFBPC9OYW1lPjwvUm93PiA8Um93PjxJZD4yMjI8L0lkPjxOYW1lPkRERDwvTmFtZT48L1Jvdz4gPFJvdz48SWQ+MzMzPC9JZD48TmFtZT5DQ0M8L05hbWU+PC9Sb3c+IDwvRGF0YT4=</Result>
 </soap:Body>
</soap:Envelope>

mceclip6.png

 

Pattern #2 (HTML Encoded Text - Attribute and Inner Text Pattern)

Here is example with encoded inner XML. Notice in below XML Result Node has Attribute named "V" when you have text like below your output will show data in column named "#text"

Sample File:

https://zappysys.com/downloads/files/test/nested-encoded.xml

 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
 <Result V="1">
&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;
&lt;Data&gt; 
 &lt;Row&gt;&lt;Id&gt;111&lt;/Id&gt;&lt;Name&gt;AAA&lt;/Name&gt;&lt;/Row&gt;
 &lt;Row&gt;&lt;Id&gt;222&lt;/Id&gt;&lt;Name&gt;DDD&lt;/Name&gt;&lt;/Row&gt;
 &lt;Row&gt;&lt;Id&gt;333&lt;/Id&gt;&lt;Name&gt;CCC&lt;/Name&gt;&lt;/Row&gt;
&lt;/Data&gt;
 </Result>
 </soap:Body>
</soap:Envelope>

mceclip5.png

 

Pattern #3 (HTML Encoded Text - No Attributes)

Here is another variation of above XML. This one doesnt have attribute inside Result Tag...when you have text like below your output will show data in column named "Result"

Sample File:

https://zappysys.com/downloads/files/test/nested-encoded-no-attr.xml

 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
 <Result>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;
&lt;Data&gt; 
 &lt;Row&gt;&lt;Id&gt;111&lt;/Id&gt;&lt;Name&gt;AAA&lt;/Name&gt;&lt;/Row&gt;
 &lt;Row&gt;&lt;Id&gt;222&lt;/Id&gt;&lt;Name&gt;DDD&lt;/Name&gt;&lt;/Row&gt;
 &lt;Row&gt;&lt;Id&gt;333&lt;/Id&gt;&lt;Name&gt;CCC&lt;/Name&gt;&lt;/Row&gt;
&lt;/Data&gt;
 </Result>
 </soap:Body>
</soap:Envelope>

mceclip4.png

Pattern #4 (XML contains result of another XML as Base64)

Here is another pattern where Result is returned as Base64 data. 

Sample File:

https://zappysys.com/downloads/files/test/nested-base64.xml

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Result>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4gPERhdGE+IDxSb3c+PElkPjExMTwvSWQ+PE5hbWU+QUFBPC9OYW1lPjwvUm93PiA8Um93PjxJZD4yMjI8L0lkPjxOYW1lPkRERDwvTmFtZT48L1Jvdz4gPFJvdz48SWQ+MzMzPC9JZD48TmFtZT5DQ0M8L05hbWU+PC9Sb3c+IDwvRGF0YT4=</Result>
  </soap:Body>
</soap:Envelope>

mceclip0.png

 

Solution - CData Pattern

Based on above 3 patterns, regardless which format you have for your XML use below steps to execute URL Join pattern (perform XML Parsing in 2 steps).

First we fetch data using normal URL... and then we pass that data to second query (see  ,join1_data='[$a.#cdata-section$]' ). Using second query we parse in Rows/Column to get the final result... 

Here is how to do it.

  1. Open ODBC Admin UI
  2. Create new Data Source and select ZappySys XML Driver
  3. Configure XML Driver UI like below. 
    For Demo use URL https://zappysys.com/downloads/files/test/nested-cdata.xml 
    Browse Filter and select very last node where your nested text is found (It can be #CData-Section OR #text or just node name like three patterns we discussed above)

    For Pattern1  (CData) $.soap:Envelope.soap:Body.Result.#cdata-section
    --OR--
    For Pattern2 (Encoded)  $.soap:Envelope.soap:Body.Result.#text
    --OR--
    For Pattern3 (Encoded + No Attribute in Root)  $.soap:Envelope.soap:Body.Result
    --OR--
    For Pattern4 (Base64)  $.soap:Envelope.soap:Body.Result


    read-xml-nested-cdata-section.png

  4. Thats it now run below Query on Preview Tab and you will see Nested XML Data is parsed in multiple rows and columns :)

    Pattern#1 Query nested XML in CData Section
    SELECT 
     b.*
    FROM $
    WITH(
     --1st level (root)--
     --src='c:\temp\sample.xml'
     
     src='https://zappysys.com/downloads/files/test/nested-cdata.xml' 
     --,metthod='GET'
     --,body='<data>xxxx some param xxxxx</data>'
     ,filter='$.soap:Envelope.soap:Body.Result.#cdata-section'
     ,alias='a'

    --2nd level--
     ,join1_data='[$a.#cdata-section$]'
     ,join1_ElementsToTreatAsArray='Row'
     ,join1_filter='$.Data.Row[*]' 
     ,join1_alias='b' 
    )

    url-join-xml-nested-text-cdata-section.png

Solution - Encoded / Base64 Pattern

Now Let's check other 3 patterns - How to query them

 

Pattern #2 (HTML Encoded Text - Attribute and Inner Text Pattern)

Write query like below for Pattern#2  (inner XML with root node which has attribute)

SELECT 
b.*
FROM $
WITH(
--1st level (root)--
--src='c:\temp\sample.xml'

src='https://zappysys.com/downloads/files/test/nested-encoded.xml' 
,filter='$.soap:Envelope.soap:Body.Result.#text'
,alias='a'

--2nd level--
,join1_data='[$a.#cdata-section$]'
,join1_ElementsToTreatAsArray='Row'
,join1_filter='$.Data.Row[*]' 
,join1_alias='b' 
)

mceclip0.png

 

parse-xml-stored-as-html-encoded-value-nested-node.png

Pattern #3 (HTML Encoded Text - No Attributes)

Write query like below for Pattern#3  (inner XML with root node without attribute)

 

SELECT 
b.*
FROM $
WITH(
--1st level (root)--
--src='c:\temp\sample.xml'

src='https://zappysys.com/downloads/files/test/nested-encoded.xml' 
,filter='$.soap:Envelope.soap:Body.Result.#text'
,alias='a'

--2nd level--
,join1_data='[$a.#cdata-section$]'
,join1_ElementsToTreatAsArray='Row'
,join1_filter='$.Data.Row[*]' 
,join1_alias='b' 
)

 

Pattern#4 - Reading nested XML data in Base64 

Here is sample query to read data which is Base64 format. Notice how we used FUN_BASE64DEC placeholder function to decode base string and again parse as XML in 2nd call (JOIN1_DATA='[$a.Result,FUN_BASE64DEC$]' )

 

SELECT b.* FROM $
WITH(
--1st level (root)--
 src='https://zappysys.com/downloads/files/test/nested-base64.xml' 
,filter='$.soap:Envelope.soap:Body'
,alias='a'

--2nd level--
,join1_data='[$a.Result,FUN_BASE64DEC$]'
,join1_ElementsToTreatAsArray='Row'
,join1_filter='$.Data.Row[*]' 
,join1_alias='b' 
)

parse-xml-data-base64-odbc-driver.png

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk