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

    shredding xml

    user8990594

      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

      statusblankettensnamntyp1utgavaID1ID2DatumperiodNr1KOLUMNNAMNET1KOLUMNNAMNET2KOLUMNNAMNET3
      AINK21SKV2004-22-02-12-0528284101538212013-03-222013PO1984062400029750012345665432

      code

       

      select      
       xt1.Status,Blankettensnamn,typ1,utgava,id1,id2,datum,period,nr1 ,xt2.*
       from oracletest1  xmlt,  
            XMLTable(XMLNAMESPACES ('http://www.w3.org/TR/html4/' as "koil"),  
                     '/root'  
                     PASSING   xmlt.xmldata
                     COLUMNS  
                            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"),  
                     '//koil:Varde'  
                     PASSING   xt1.group_xml  
                     COLUMNS  
                     termid    VARCHAR2(15)  PATH 'koil:TermId',  
                     vardenum  varchar2(20)      PATH 'koil:VardeNum' ,
                     OpId varchar2(50) path 'koil:OpId'
                     ) xt2
      

       

      Result Obtained

      StatusBlankettensnamntyp1utgavaID1ID2DatumperiodNr1TermIdVardeNumOpId
      AINK2ISKV2004-22-02-12-0528284101538212013-03-222013P0198406240000KOLUMNNAMNET1297500drty
      AINK2ISKV2004-22-02-12-0528284101538212013-03-222013P0198406240000KOLUMNNAMNET2123456drty
      AINK2ISKV2004-22-02-12-0528284101538212013-03-222013P0198406240000KOLUMNNAMNET3654321drty

       

      XML File

       

       

      <root xmlns:koil="http://www.w3.org/TR/html4/">
      <koil:Data>
        <koil:Status>A</koil:Status>
        <koil:Blankettensnamn>INK2</koil:Blankettensnamn>                                              
        <koil:Typ1>I</koil:Typ1>
        <koil:Utgava>SKV2004-22-02-12-05</koil:Utgava>
        <koil:ID1>28284</koil:ID1>                                                                        
        <koil:ID2>10153821</koil:ID2>
        <koil:Datum>2013-03-22</koil:Datum>                             
        <koil:Period>2013P0</koil:Period>                                                                
      
      
      <koil:Nr1>198406240000</koil:Nr1>                                                                 
        <koil:Varden>
        <koil:Varde>
        <koil:TermId>KOLUMNNAMNET1</koil:TermId>                                                          
      
      
        <koil:VardeNum>297500</koil:VardeNum>                                                             
        <koil:OpId>drty</koil:OpId>
        </koil:Varde>
        <koil:Varde>
        <koil:TermId>KOLUMNNAMNET2</koil:TermId>                                                          
      
      
        <koil:VardeNum>123456</koil:VardeNum>                                                             
        <koil:OpId>drty</koil:OpId>
        </koil:Varde>
        <koil:Varde>
        <koil:TermId>KOLUMNNAMNET3</koil:TermId>                                                          
      
      
        <koil:VardeNum>654321</koil:VardeNum>                                                             
        <koil:OpId>drty</koil:OpId>
        </koil:Varde>
        </koil:Varden>
      </koil:Data>
      </root>
      

       

      Thanks

        • 1. Re: shredding xml
          Jason_(A_Non)

          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
            user8990594

            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
              odie_63

              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
                user8990594

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