6 Replies Latest reply on May 11, 2010 10:11 AM by jdoyle

    How to 'flatten' an XML file?

    carlossc

      Hello:

       

      How can I 'flatten' an XML file to turn it into a relational table?

       

      I explain:

      I have an XML file similar to this:

       

      <?xml version="1.0" encoding="UTF-8" ?>
      <shop>
          <url1>http://www.....</url1>
          <url2>http://www.....</url2>
          <category>
              <category_id>1</category_id>
              <category_name>...</category_name>
              <products>
                  <product>
                      <product_id>1</product_id>
                      <product_name>...</product_name>
                  </product>
                  <product>
                       <product_id>2</product_id>
                       <product_name>...</product_name>
                   </product>

               </products>

          </category>

          <category>

         .....

       

      and I would like to create a "View model" with a "View" with the columns:

      "category_id", "category_name", "product_id" and "product_name".

      This view should have a row for each product of the XML, with the id and the name of the category that each product belongs to.

       

      When I Import the "XML Schema as Relational Source Model", Teiid Desiger creates three tables: "shop", "category" and "product".

      It adds the attribute "mmid" to each table an creates foreign keys between product and category; and category and shop.

       

      I thought that I could create the "View" that I wanted with a JOIN using the mmid attributes in the JOIN condition. I.e.:

      SELECT category_id, category_name, product_id, product_name
      FROM product p INNER JOIN category c ON p.category_mmid = c.mmid;

       

      But this query doesn't return anything because the "mmid" values are empty. I thought that mmid had to contain the XPath route to each node when using the XML-Relational File connector (I have the same problem using the XML-Relational HTTP connector)

       

      So:

      1. Is there is another way of achieving what I want to do, instead of using a JOIN?
      2. Maybe the JOIN approach is correct, but I have to do something else so the "mmid" columns have values in them?

       

      Thanks in advance.

       

      My environment:

      S.O: Windows XP SP3.

      Teiid Designer 6.2.0

        • 1. Re: How to 'flatten' an XML file?
          jdoyle

          Hi Carlos,

           

          First, which connector are you using?

           

          The mmid values are populated by the connector as the XML is read by the connector.  If the value are null that would indicate some kind of problem.  Perhaps you could run a query that is not a join and just selects the mmid to confirm if they have a value or not.

           

          I would like to see what queries are produced by the engine from your query.  Depending upon the version of Teiid you are using, there is a different syntax to see the query plan. If it's 6.2 append OPTION SHOWPLAN to the end of your query.

           

          ~john

          • 2. Re: How to 'flatten' an XML file?
            carlossc

            Hi John,

             

            I'm using the 'XML-Relational File' connector.

             

            If I execute

            select category_mmid, mmid, product_id, product_name from "testVDB"."products.product" OPTION SHOWPLAN

            In the result, the values of 'mmid' and 'category_mmid' are empty.

             

            In the 'Show the query plan document' panel there is:

             

            <?xml version="1.0" encoding="UTF-8"?>
            <node name="Relational Plan">
              <properties>
                <property name="Output Columns">
                  <collection>
                    <value>category_mmid (string)</value>
                    <value>mmid (string)</value>
                    <value>product_id (string)</value>
                    <value>product_name (string)</value>
                  </collection>
                </property>
              </properties>
              <node name="Access [SELECT testVDB.products.product.category_mmid, testVDB.products.product.mmid, testVDB.products.product.product_id, testVDB.products.product.product_name FROM testVDB.products.product]">
                <properties>
                  <property name="Output Columns">
                    <collection>
                      <value>category_mmid (string)</value>
                      <value>mmid (string)</value>
                      <value>product_id (string)</value>
                      <value>product_name (string)</value>
                    </collection>
                  </property>
                  <property name="Source Query" value="SELECT testVDB.products.product.category_mmid, testVDB.products.product.mmid, testVDB.products.product.product_id, testVDB.products.product.product_name FROM testVDB.products.product"/>
                  <property name="Model Name" value="products"/>
                  <property name="Cost Estimates">
                    <collection>
                      <value>Estimated Node Cardinality: -1.0</value>
                    </collection>
                  </property>
                  <property name="Statistics">
                    <collection>
                      <value>Node Output Rows: 0</value>
                      <value>Node Process Time: 0</value>
                      <value>Node Cumulative Process Time: 0</value>
                      <value>Node Cumulative Next Batch Process Time: 0</value>
                      <value>Node Next Batch Calls: 0</value>
                      <value>Node Blocks: 0</value>
                    </collection>
                  </property>
                </properties>
              </node>
            </node>

             

            I attach the XSD file that I create the 'Relational Source Model from'. And the XML file that contains the data.

            • 3. Re: How to 'flatten' an XML file?
              jdoyle

              That query plan is not what I would expect to see.  I've reproduced your issue with the files you provided and amy trying to track down the issue.  I'll reply again when I've figured it out.

               

              ~jd

              • 4. Re: How to 'flatten' an XML file?
                carlossc

                Thanks for the effort you are putting into this.

                • 5. Re: How to 'flatten' an XML file?
                  carlossc

                  Hi John:

                   

                  Should I report a bug in the JIRA of Teiid?

                   

                  Carlos.

                  • 6. Re: How to 'flatten' an XML file?
                    jdoyle

                    Please do log a Jira,  I don't have much time to dedicate to this issue right now.  I've looked at this a bit and changed the XPaths (contained in the Name in Source properties) for the Shop table to get the expected results, but I've been unable to get the correct result for the Category or Product tables.

                     

                    There appears to be a defect in the connector because to get the correct values from the Shop table I had to use incorrect XPaths.  For the Shop table Name in Source I used /shop, but for the mmid I used /shop/@com.metamatrix.xml.xpathpart, shich is incorrect but get the correct result.  /shop/url1/text() and /shop/url2/text() produced the correct results for the url values.

                     

                    ~jd