Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akki_raghavan
New Contributor II

Loading an XML field in SQL Server into Qlik Sense

Hi,

I have a SQL server field that displays  entire XML scripts in each row as a link. So in SQL server if you click on that link it expands the xml script into a seperate tab so it is easy to read the script. I was able to load the field into Qlik Sense. I was wondering if there is a way to make the xml more presentable into separate lines.

Thanks

2 Replies
MVP
MVP

Re: Loading an XML field in SQL Server into Qlik Sense

It is possible to read the content of a field and interpret it just like it would come from a file. To be able to do that you have to use the FROM_FIELD instead of FROM:

LOAD

  ......

FROM_FIELD ('ATABLE','AFIELD') (xmlSimple, Table is [CATALOG/CD]);

Here is a full example:

XMLTABLE:   // CONTAINS ALL THE XML IN ONE SINGLE ROW IN A SINGLE FIELD

LOAD Concat(XML,'',R#) AS XMLFIELD; // Merge all rows into one row

LOAD RecNo() AS R#,XML // Need the R# as ordering for the Concat function

INLINE [

XML

<CATALOG>

  <CD>

    <TITLE>Empire Burlesque</TITLE>

    <ARTIST>Bob Dylan</ARTIST>

    <COUNTRY>USA</COUNTRY>

    <COMPANY>Columbia</COMPANY>

    <PRICE>10.90</PRICE>

    <YEAR>1985</YEAR>

  </CD>

  <CD>

    <TITLE>Hide your heart</TITLE>

    <ARTIST>Bonnie Tyler</ARTIST>

    <COUNTRY>UK</COUNTRY>

    <COMPANY>CBS Records</COMPANY>

    <PRICE>9.90</PRICE>

    <YEAR>1988</YEAR>

  </CD>

  <CD>

    <TITLE>Greatest Hits</TITLE>

    <ARTIST>Dolly Parton</ARTIST>

    <COUNTRY>USA</COUNTRY>

    <COMPANY>RCA</COMPANY>

    <PRICE>9.90</PRICE>

    <YEAR>1982</YEAR>

  </CD>

</CATALOG>

];


LOAD

  TITLE,

  ARTIST,

  COUNTRY,

  COMPANY,

  PRICE,

  YEAR

FROM_FIELD('XMLTABLE','XMLFIELD') (xmlSimple, table is [CATALOG/CD]);

You will replace the lines from 1 to 32 with your SQL SELECT for SQL Server.

akki_raghavan
New Contributor II

Re: Loading an XML field in SQL Server into Qlik Sense

Thanks Petter for the quick response. The thing with the XML field is, each row in the field are different  sql xml files. So it becomes cumbersome to manually read the contents in each xml and create fields in qlik sense for each of these XML files. So, I was thinking if I can even open the xml link on a seperate tab or a window, something that simply expands the script would be fine.

Sorry, I can't add screenshots to explain better. Its private data.

Community Browser