Loading an XML field in SQL Server into Qlik Sense

[Anonymous]

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

[petter]

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.

[Anonymous]

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.