This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 12:23 PM by 972590 RSS

XML extract

972590 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Still having some issues with the XML query
  • 7. Re: XML extract
    odie_63 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    The rule is ProductObjects node will have atelast one ProductObject element wrapped init.
  • 14. Re: XML extract
    odie_63 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points