2 Replies Latest reply: Apr 13, 2018 11:14 AM by Akshay Anandbabu RSS

    Loading an XML field in SQL Server into Qlik Sense

    Akshay Anandbabu

      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

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

          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.

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

              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.