XML string parsing, in QV LOAD script

[sbumeshqlik]

Hello,

In my qv Load script, I have string intilized to some XML content.

I was able to store  that in a load table. but no clue,how   to parse  that string xml..

Any help would be greatly appreciated.

Listed below QV Script

====================================================================

vxmlstr = '<?xml version="1.0" encoding="utf-8"?>

<ArrayOftCountryCodeAndName>

  <tCountryCodeAndName>

    <sISOCode>GB</sISOCode>

    <sName>United Kingdom</sName>

  </tCountryCodeAndName>

  <tCountryCodeAndName>

    <sISOCode>GS</sISOCode>

    <sName>South Georgia &amp; South Sandwich Islands</sName>

  </tCountryCodeAndName>

  <tCountryCodeAndName>

    <sISOCode>IO</sISOCode>

    <sName>British Indian Ocean Territory</sName>

  </tCountryCodeAndName>

</ArrayOftCountryCodeAndName>

' ;

//Load XMl String

XMLFILE:

LOAD * INLINE [

     XML

    $(vxmlstr)

];

[marcus_sommer]

Maybe this is helpful: Re: XML part of XLS data - how to extract

- Marcus

[petter · Accepted solution]

I found your case interesting - so I got carried away and tested your specific Web Service with different countries and discovered that it was a couple of additional challenges there. The Web Service will for instance not always give back all fields for all airports in a country. So that has to be handled dynamically or the table being read will have rows where the columns get offset and hence corrupted.

Now I tested it with various countries and even the USA with probably the most airports in the world ~2000 it works now. Here there was an additional challenge in that single quotation marks is part of some airport's name. Which initially I didn't handle but modified my code later to accept that.

Enjoy - find my solution attached

[Gysbert_Wassenaar]

There's a FROM_FIELD option that you can use to load a table of data from a field from another table. See this discussion for an example: Re: Reading multiple XML nodes

You can find more discussion on this subject with this search: https://community.qlik.com/search.jspa?q=xml+from_field

[sbumeshqlik]

Petter,

Thank you, so much.

[diwaskarki]

Hello  petter-s,

I have a similar situation. I have an xml field in my db2 database. I need to parse strings that are stored as list in the xml.  "Attribute" is the field in my database. It looks like this:

<Attributes>

     <Map>

          <entry key="Removed access"

          <value>

               <List>

                    <String>data<String1>

                    <String>data1<String1>

                    <String>data2<String>

I need to parse data,data1, data2 into qlikiview. I have extracted single values from a Map using textbetween function.

But it doesnt seem to work for multiple entries in the list. I would greatly appreciate the help. Thanks  

[diwaskarki]

Hello gwassenaar,

I have a similar situation. I have an xml field in my db2 database. I need to parse strings that are stored as list in the xml.  "Attribute" is the field in my database. It looks like this:

<Attributes>

     <Map>

          <entry key="Removed access"

          <value>

               <List>

                    <String>data<String1>

                    <String>data1<String1>

                    <String>data2<String>

I need to parse data,data1, data2 into qlikiview. I have extracted single values from a Map using textbetween function.

But it doesnt seem to work for multiple entries in the list. I would greatly appreciate the help. Thanks