1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 2:23 PM by 972590 RSS

    XML extract

    972590
      Hi,

      I need some help in extracting the DATA from an XML. I have the following example XML, and the object element can go multi level deep just like the following. Please share your ideas or any information in extracting this data by an SQL query. I am usig Oracle version 11gR2.

      <objects>
      <name>foo-1</name>
      <object>
      <name>foo-2</name>
      <object>
      <name>foo-3</name>
      <object>
      <name>foo-4</name>
      </object>
      </object>
      </object>
      </objects>

      in some cases th XML may be simple like

      <objects>
      <name>foo-1</name>
      <object>
      <name>foo-2</name>
      </object>
      </objects>

      Thanks
      Ed
        • 1. Re: XML extract
          odie_63
          Hi,

          You want to "extract" data. That's a pretty vague requirement.

          What do you want the output to look like?
          Do you want the result in relational format?
          Do you want to put all <name> elements in a collection, in a table?

          Where does the XML reside initially? Table? Which datatype?

          Please answer those questions and we'll be able to suggest the best way of doing it.
          • 2. Re: XML extract
            972590
            Thanks you very much for the reply.

            1. I do not want this data to be displayed in an hierarchial format. I need to extract the data and load it into various tables in Oracle.

            2. There is parent-child relation in this XML. For example
            <objects>
            <object>           -- is a Parent to foo-2
            <name>foo-1</name>
            <object>          -- is a Parent to foo-3/Child to foo-1     
            <name>foo-2</name>
            <object>     -- is a Child to foo-2
            <name>foo-3</name>
            </object>
            <object>     -- is a Child to foo-2
                 <name>foo-4</name>
            </object>
            </object>
            </object>
            </objects>

            3. If possible, I would like to store it in a collection.

            4. The XML comes from an .Net Application. The .Net application calls Oracle stored procedure, with a CLOB parameter, and should eventually extract the data and store the data in variuous tables.

            I am not sure I explained it properly. If not please let me know. And thank you very much in advance for any help.

            Thanks
            Ed
            • 3. Re: XML extract
              odie_63
              OK, so collection or tables?

              Here's something to start with.
              It's very simple but given the sample XML I can't really suggest more :
              SQL> var p_clob clob
              
              SQL> begin
                2    :p_clob := '<objects>
                3  <name>foo-1</name>
                4  <object>
                5  <name>foo-2</name>
                6  <object>
                7  <name>foo-3</name>
                8  <object>
                9  <name>foo-4</name>
               10  </object>
               11  </object>
               12  </object>
               13  </objects>';
               14  end;
               15  /
              
              PL/SQL procedure successfully completed.
              
              SQL> SELECT x.*
                2  FROM XMLTable(
                3         '/objects//name'
                4         passing xmlparse(document :p_clob)
                5         columns "NAME" varchar2(30) path '.'
                6       ) x
                7  ;
              
              NAME
              ------------------------------
              foo-1
              foo-2
              foo-3
              foo-4
              P_CLOB will be the input parameter of your procedure. You can use the result of the SELECT to fill a collection, or directly perform an INSERT into a target table.

              If you can provide a more complex XML file and explain how you want to load it into different tables then I'd be glad to make further suggestions.
              • 4. Re: XML extract
                972590
                Thank you so much. This should help me to bgin with. I will try to parse the actual XML using what you have suggested, and will certainly let you know whether I am successfull or not. I need to get this XML from the Apps Team. But thank you very much and will definitely post the actual XML if I am not successfull.

                Thanks
                Ed
                • 5. Re: XML extract
                  972590
                  Hi,

                  I have tried several ways to extract data, from the following XML, but could not succeed. I got Oracle errors. Please take a look at the following XML. I was able to extract to some extent. But due to recurring node (ProductObject) I got Oracle errors. This node can recur multiple times. Just like a parent-child nodes. Please help me with your ideas

                  XML
                  *****
                  <Product>
                  <Header>
                  <Version>1.1</Version>
                  </Header>
                  <Pld>
                  <ProductObject>
                  <Name>XYZTEST</Name>
                  <ProductType>TESTTYPE</ProductType>
                  <ProductID>B000148</ProductID>
                  <ProductAccount>C10023</ProductAccount>
                  <ProductLocations>
                  <ProductLocation>
                  <ProductLocationType>L</ProductLocationType>
                  <Address1>43414 SP LN</Address1>
                  <Address2>NULL</Address2>
                  <City>CHANTILLI</City>
                  <State>VA</State>
                  <PostalCode>45245</PostalCode>
                  <PostalPlus>1123</PostalPlus>
                  <CountryCode>USA</CountryCode>
                  </ProductLocation>
                  <ProductLocation>
                  <ProductLocationType>L1</ProductLocationType>
                  <Address1>43415 SPRINGFIELD LN</Address1>
                  <Address2>NULL1</Address2>
                  <City>CHANTILLI1</City>
                  <State>VA1</State>
                  <PostalCode>45241</PostalCode>
                  <PostalPlus>1121</PostalPlus>
                  <CountryCode>USA1</CountryCode>
                  </ProductLocation>
                  </ProductLocations>
                  <ProductContacts>
                  <ProductContact>
                  <ProductContactType>L</ProductContactType>
                  </ProductContact>
                  <ProductContact>
                            <ProductContactType>P</ProductContactType>
                  </ProductContact>
                  </ProductContacts>
                  <ProductObjects>
                  <ProductObject>
                  <Name>XYZTEST-2</Name>
                  <ProductType>TESTTYPE-2</ProductType>
                  <ProductID>B000148-2</ProductID>
                  <ProductAccount>C10023-2</ProductAccount>
                  <ProductLocations>
                  <ProductLocation>
                  <ProductLocationType>L-2</ProductLocationType>
                  <Address1>43414 SPRINGFIELD LN-2</Address1>
                  <Address2>NULL-2</Address2>
                  <City>CHANTILLI-2</City>
                  <State>VA-2</State>
                  <PostalCode>45040-2</PostalCode>
                  <PostalPlus>1123-2</PostalPlus>
                  <CountryCode>USA-2</CountryCode>
                  </ProductLocation>
                  </ProductLocations>
                  <ProductContacts>
                  <ProductContact>
                  <ProductContactType>L-2</ProductContactType>
                  </ProductContact>
                  </ProductContacts>
                       <ProductObject>
                       <Name>XYZTEST-3</Name>
                       <ProductType>TESTTYPE-3</ProductType>
                       <ProductID>B000148-3</ProductID>
                       <ProductAccount>C10023-3</ProductAccount>
                       <ProductLocations>
                            <ProductLocation>
                            <ProductLocationType>L-3</ProductLocationType>
                            <Address1>43414 SPRINGFIELD LN-3</Address1>
                            <Address2>NULL-3</Address2>
                            <City>CHANTILLI-3</City>
                            <State>VA-3</State>
                            <PostalCode>45040-3</PostalCode>
                            <PostalPlus>1123-3</PostalPlus>
                            <CountryCode>USA-3</CountryCode>
                            </ProductLocation>
                       </ProductLocations>
                       <ProductContacts>
                       <ProductContact>
                       <ProductContactType>L-3</ProductContactType>
                       </ProductContact>
                       </ProductContacts>
                  </ProductObject>
                  </ProductObject>
                  </ProductObjects>
                  </ProductObject>
                  </Pld>
                  </Product>

                  Thanks in advance
                  Ed
                  • 6. Re: XML extract
                    972590
                    Still having some issues with the XML query
                    • 7. Re: XML extract
                      odie_63
                      969587 wrote:
                      I have tried several ways to extract data, from the following XML, but could not succeed. I got Oracle errors.
                      What errors?

                      Please also post what you've tried so far, since you still haven't explained it clearly, it'll give us an idea about the kind of output you need.
                      • 8. Re: XML extract
                        972590
                        Thanks for the reply. I have tried buinding the following query and got stuck at place where <ProductObject> node is iterated and has parent-child relation in the XML. please see the XML and ERROR that I was getting
                        Please note I have declared a variable p_clob as a CLOB type.

                        SELECT X.Version,
                        X.ProductName,
                        X.ProductType,
                        X.ProductID,
                        X.ProductAccountCode,
                        Y.ProducttLocationType,
                        Y.ProductAddress1,
                        Y.ProductAddress2,
                        Y.ProductCity,
                        Y.ProductState,
                        Y.ProductPostalCode,
                        Y.ProductPostalPlus,
                        Y.ProductCountryCode,
                        Z.ProdContactType
                        FROM XMLTABLE (
                        '/Product'
                        PASSING XMLPARSE (DOCUMENT p_clob) AS
                        COLUMNS Ver VARCHAR2 (100) PATH 'Product/Header/Version',
                        ProductName VARCHAR2 (100)
                        PATH 'Product/pld/ProductObject/Name',
                        ProductType VARCHAR2 (100)
                        PATH 'Product/pld/ProductObject/ProductType',
                        ProductID VARCHAR2 (10)
                        PATH 'Product/pld/ProductObject/ProductID',
                        ProductAccountCode VARCHAR2 (10)
                        PATH 'Product/pld/ProductObject/ProductAccount',
                        ProdXML1 XMLTYPE
                        PATH 'Product/pld/ProductObject/ProductLocations',
                        ProdXML2 XMLTYPE
                        PATH 'Product/pld/ProductObject/ProductContacts',
                        ProdXML3 XMLTYPE
                        PATH 'Product/pld/ProductObject/ProductObjects') X,
                        XMLTABLE (
                        'for $ProdLocation in $PRODLOC/ProductLocation
                        return <row>
                        {
                        $ProdLocation
                        }
                        </row>'
                        PASSING X.ProdXML1 AS PRODLOC
                        COLUMNS ProductLocationType VARCHAR2 (10)
                        PATH './ProductLocationType',
                        ProductAddress1 VARCHAR2 (10) PATH './Address1',
                        ProductAddress2 VARCHAR2 (10) PATH './Address2',
                        ProductCity VARCHAR2 (10) PATH './City',
                        ProductState VARCHAR2 (10) PATH './State',
                        ProductPostalCode VARCHAR2 (10) PATH './PostalCode',
                        ProductPostalPlus VARCHAR2 (10) PATH './PostalPlus',
                        ProductCountryCode VARCHAR2 (10) PATH './CountryCode') Y,
                        XMLTABLE (
                        'for $ProdContact in $PRODCNTCT/ProductContact
                        return <row>
                        {
                        $ProdContact
                        }
                        </row>'
                        PASSING X.ProdXML2 AS PRODCNTCT
                        COLUMNS ProdContactType VARCHAR2 (10) PATH './/ProductContactType') Z;

                        ERROR
                        ********
                        ORA-19279: XPTY0004 - XQuery dynamic type mismatch

                        Thanks
                        Ed
                        • 9. Re: XML extract
                          odie_63
                          Thanks for the details.
                          Your query has a lot of syntax errors/invalid identifiers/wrong xpath so I doubt it's the one you're actually running.


                          How many level of recursivity can you have at most?
                          Sometimes, <ProductObject> elements are wrapped in a <ProductObject s > parent, sometimes they're not. What is the rule?

                          Do you want to keep track of the parent/child relationship between nested <ProductObject>, for example by generating a PARENT_PRODUCTID column?

                          Edited by: odie_63 on 13 févr. 2013 20:18
                          • 10. Re: XML extract
                            972590
                            Thanks again for the reply. Yes there are atleast 2 leveles (for sure) of paranet-child relation. Please see the following picture, and yes we need to keep track of the parent-child relation at each level. i am sorry for not explaining in details.

                            Product (Parent)
                            |
                            |___Product (Parent/Child)
                            |
                            |_______Product (Parent/Child)
                            |
                            |_______Product (Child)
                            • 11. Re: XML extract
                              odie_63
                              Could you also answer this question?
                              Sometimes, <ProductObject> elements are wrapped in a <ProductObject s > parent, sometimes they're not. What is the rule?
                              • 12. Re: XML extract
                                972590
                                Yes, the outer (meaning the ProductObject element in the begining of the XML) is the Product group element. And a ProductObject (which can be a Parent/Child) is wrapped in the ProductiObJects like


                                ProductObject -- ProductGroup
                                _______ProductObjects -- Group/Member
                                ______________ProductObject -- Group/Member
                                __________________ProductObject -- Member

                                Thanks
                                Ed
                                • 13. Re: XML extract
                                  972590
                                  The rule is ProductObjects node will have atelast one ProductObject element wrapped init.
                                  • 14. Re: XML extract
                                    odie_63
                                    I'll post an example soon.

                                    BTW, by any chance, do you have an XML schema (XSD) for the input XML?
                                    It would simplify a lot of things.
                                    1 2 Previous Next