4 Replies Latest reply: Jun 28, 2013 8:03 AM by user8990594 RSS

    shredding xml


      I am trying to shred an XML in to relational table format. my desired output is as follows but i am not able to achieve it. I am posting the xml file, the desired result, the code i tried and the result which i am gettting. please help me to solve this issue. I hope i have formatted the question properly this time to get some idea from experts.

      Desired Result




       xt1.Status,Blankettensnamn,typ1,utgava,id1,id2,datum,period,nr1 ,xt2.*
       from oracletest1  xmlt,  
            XMLTable(XMLNAMESPACES ('http://www.w3.org/TR/html4/' as "koil"),  
                     PASSING   xmlt.xmldata
                            Status varchar2(100) path '//koil:Status',
                                Blankettensnamn varchar2(100) path '//koil:Blankettensnamn',
                                Typ1 varchar2(100) path '//koil:Typ1',
                                Utgava varchar2(100) path '//koil:Utgava',
                                ID1 varchar2(100) path '//koil:ID1',
                                 ID2 varchar2(100) path '//koil:ID2',
                                 Datum varchar2(100) path '//koil:Datum',
                                 Period varchar2(100) path '//koil:Period',
                                  Nr1 varchar2(100) path '//koil:Nr1',
                                  group_xml       XMLTYPE       PATH '//koil:Varde'  
                     ) xt1,
            XMLTable(XMLNAMESPACES ('http://www.w3.org/TR/html4/' as "koil"),  
                     PASSING   xt1.group_xml  
                     termid    VARCHAR2(15)  PATH 'koil:TermId',  
                     vardenum  varchar2(20)      PATH 'koil:VardeNum' ,
                     OpId varchar2(50) path 'koil:OpId'
                     ) xt2


      Result Obtained



      XML File



      <root xmlns:koil="http://www.w3.org/TR/html4/">



        • 1. Re: shredding xml

          For possible scenarios, are the three column names returned by the SELECT statement always the same and will the koil:TermId always be the same for the XML being processed?


          In other words, is the XML you provided representative of all XML or a single case and is your SQL statement supposed to return three values or is the number of values dependent upon the number of koil:Varde nodes?

          • 2. Re: shredding xml

            Thanks for replying . the  SQL needs to return values depending upon the number of koil:Varde nodes. the maximum possible number of koil:Varde nodes in the XML is 3. Please let me know if its not clear. It would be great if you could provide me way to proceed with this task.

            • 3. Re: shredding xml

              the maximum possible number of koil:Varde nodes in the XML is 3.

              In this case you can use a positional predicate to access a specific node occurrence :

              select x.*

              from oracletest1 t

                 , xmltable(

                     xmlnamespaces('http://www.w3.org/TR/html4/' as "k")

                   , '/root/k:Data'  

                     passing t.xmldata

                     columns Status          varchar2(100) path 'k:Status'

                           , Blankettensnamn varchar2(100) path 'k:Blankettensnamn'

                           , Typ1            varchar2(100) path 'k:Typ1'

                           , Utgava          varchar2(100) path 'k:Utgava'

                           , KOLUMNNAMNET1   number        path 'k:Varden/k:Varde[1]/k:VardeNum'

                           , KOLUMNNAMNET2   number        path 'k:Varden/k:Varde[2]/k:VardeNum'

                           , KOLUMNNAMNET3   number        path 'k:Varden/k:Varde[3]/k:VardeNum'

                   ) x


              • 4. Re: shredding xml

                Thanks a lot it worked. I will read about positional predicate and learn more about XMLTable.