4 Replies Latest reply on Aug 18, 2016 8:06 PM by Karen Huang

    How to handle xml data collected via Tableau WDC?

    Karen Huang

      I'm testing Tableau WDC to collect weather data from BOM website for a business report. Using the json connector provided by WDC SDK, I can fetch data from the remote web server and get the following four data columns:

       

      query_count

      query_created

      query_lang

      query_results_body

       

      Xml data actually appeared in column query_results_body. However they are still in xml format as one big chuck, not in tabular data format. I am analyst with little xml data experience. Wondering how to import xml data so that they are converted to rows and columns?

       

      Thanks in advance.

       

      tableau wdc xml.jpg

        • 1. Re: How to handle xml data collected via Tableau WDC?
          Tom W

          Hi Karen,

          So, you've gotten part way there. Your WDC is pulling the raw JSON into Tableau (not xml).

           

          The whole premise of a WDC is that it connects to a web based source (like the BOM), gets a response, manipulates that response into a format which is usable in Tableau. This last piece is the bit you're missing right now. A complete solution would actually import columns into tableau like lat,long,cloud,dewpt etc.

           

          Here's an example provided by Tableau of a json connector - webdataconnector/jsonConnector.html at master · tableau/webdataconnector · GitHub

          And an associated discussion thread - JSON Sample Connector Thread

          If you use this connector, it will actually attempt to interpret the JSON source and flatten it out into something Tableau can interpret as dimensions and measures. I tried it with your example URL and sure enough;

           

          Neat right?

           

          This is where it gets tricky though. If it doesn't interpret the results correctly or you need something customized, you'll need to dig into the code and that will require programming experience. Thus, I hope the sample connector gives you everything you need out of the box!

          1 of 1 people found this helpful
          • 2. Re: How to handle xml data collected via Tableau WDC?
            Karen Huang

            Thanks Tom. Sorry for the late reply.

            You are right that this is json not xml . Tried it again using the Connector you pointed me to but still got a big chunk data back in dimension 'Query results body'. Seems a bit of programming in html is my only option..?

            Modified a couple of lines in getColumnHeaders by adding filed names and filed types required for weather data:

            jsoncon1.png

            The fields created can be imported into Tableau as dimensions or measures. It's not a problem. The tricky part to me is how to handle data in one of the functions in the connector. Not sure it should be done in getTableData() or  _retrieveJsonData()... Need urgent self training to write the codes. Hehe.

            Any advice?

            • 3. Re: How to handle xml data collected via Tableau WDC?
              Tom W

              Hi Karen,

              If you use the sample connector (the first link I sent) with your BOM URL, you shouldn't see a result returned as 'Query Body', you should see the columns exactly as I have in my screenshot. Either you aren't following those steps correctly or you're using a different URL.

              • 4. Re: How to handle xml data collected via Tableau WDC?
                Karen Huang

                Um... Just tested using the same BOM link. Here are my steps:

                1. New Tableau workbook and new data source

                2. In 'Web Data Connector' window entered url of the sample connector you provided. The only change I made was connector version from 1.1.1 to 1.1.0 to eliminate an error.

                jsonconn2.png

                3. Entered url for BOM json file and click 'Submit'.

                 

                Any idea what could go wrong, Tom? Thanks in advance.