1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

XML Parsing

Discussion in 'SQL PL/SQL' started by Vicky, Feb 11, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can anyone explain me, how to parse and get values from a xml file in oracle in detail ?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check this also.... It may help you
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx Reddy., Do you know, how the keyword "dbms_xmldom.item" and " dbms_xslprocessor.valueOf" works.,.,.,???
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    see link .. http://www.oracle-base.com/articles/9i/parse-xml-documents-9i.php

    examples

    Code (SQL):

    SET serveroutput ON
    DECLARE
      i dbms_xmlgen.ctxHandle;
      xm CLOB;
      sql_txt varchar2(32767) := q'{select 1 as a, 's' as b from dual union all
                                    select 2     , '
    f'      from dual union all
                                    select 3     , '
    g'      from dual union all
                                    select 4     , '
    o'      from dual
    }'
    ;
    BEGIN
      -- Test statements here
      dbms_output.put_line('Print text query : '||chr(10)||sql_txt);
      dbms_lob.createtemporary(lob_loc => xm,cache => TRUE);
      i:=dbms_xmlgen.newContext(sql_txt);
      DBMS_XMLGEN.setRowSetTag (i,'TD');
      DBMS_XMLGEN.SETROWTAG (i,'TR');

      dbms_xmlgen.getXML(i,xm);
        -- only this  exmpale
      dbms_output.put_line ('XML is '||chr(10)||xm);  
        -- only this  exmpale  
      DBMS_XMLGEN.CLOSECONTEXT (i);
    END;
    /

     
    Code (SQL):

    SET pagesize 0
    SET serveroutput ON
    SET feedback off
    DECLARE
     rc sys_refcursor;
     doc DBMS_XMLDOM.DOMDocument;
     docc CLOB;
     BEGIN      
          EXECUTE immediate q'{alter session set NLS_DATE_FORMAT='dd.mm.yyyy hh24:mi:ss'}';
          dbms_lob.createtemporary (lob_loc => docc,cache => TRUE);      
          OPEN rc FOR SELECT * FROM ( SELECT
                                            ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.LAST_ANALYZED
                                      FROM user_tables ut
                                      WHERE
                                           rownum < 10                                  
                                    );
          doc := DBMS_XMLDOM.NewDOMDocument(xmltype(rc));
          dbms_xmldom.writeToClob(doc,docc);
          dbms_output.put_line (docc);
          dbms_lob.freetemporary (lob_loc => docc);
          close rc;
     END;
    /

     

    parsing ...transform

    Code (SQL):

        -- only this  exmpale
    DECLARE

      h_xmlgen   dbms_xmlgen.ctxHandle;

      indoc      CLOB;
      xsldoc     VARCHAR2(16384);
      myParser   DBMS_XMLPARSER.parser;
      indomdoc   DBMS_XMLDOM.DOMDocument;
      xsltdomdoc DBMS_XMLDOM.DOMDocument;
      xsl        DBMS_XSLPROCESSOR.stylesheet;
      outdomdocf DBMS_XMLDOM.DOMDocumentFragment;
      outnode    DBMS_XMLDOM.DOMNode;
      proc       DBMS_XSLPROCESSOR.processor;
      buf        CLOB;
     
    BEGIN
       
      -- generating xml
      dbms_lob.createtemporary(lob_loc => indoc,cache => TRUE);
     
      h_xmlgen := dbms_xmlgen.newContext('  select
                    ut.TABLE_NAME,
                    ut.TABLESPACE_NAME,
                    ut.STATUS,
                    ut.PCT_FREE,
                    ut.PCT_USED                
      from user_tables ut
                    '
    );
                   
      DBMS_XMLGEN.setRowSetTag (h_xmlgen,'Row');
      DBMS_XMLGEN.SETROWTAG (h_xmlgen,'Cell');

      dbms_xmlgen.getXML(h_xmlgen,indoc);
     

      dbms_output.put_line ('Src xml '||chr(10)||indoc);
    xsldoc := q'{<?xml version="1.0"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>Lori Turner</Author>
      <LastAuthor>Lori Turner</LastAuthor>
      <Created>2001-02-06T17:09:39Z</Created>
      <LastSaved>2001-02-06T17:14:33Z</LastSaved>
      <Company>Microsoft</Company>
      <Version>10.2508</Version>
     </DocumentProperties>
     <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
      <DownloadComponents/>
      <LocationOfComponents HRef="file:///i:\"/>
     </OfficeDocumentSettings>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>6720</WindowHeight>
      <WindowWidth>9408</WindowWidth>
      <WindowTopX>192</WindowTopX>
      <WindowTopY>96</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Borders/>
       <Font/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
      <Style ss:ID="s22">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
       <Font ss:Bold="1"/>
       <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="s23">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
      </Style>
      <Style ss:ID="s24">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
       <Font ss:Bold="1"/>
       <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
       <NumberFormat ss:Format="0%"/>
      </Style>
      <Style ss:ID="s25">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <NumberFormat ss:Format="0%"/>
      </Style>
      <Style ss:ID="s26">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
       <Font ss:Bold="1"/>
       <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="s27">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
      </Style>
      <Style ss:ID="s28">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
      </Style>
      <Style ss:ID="s29">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
       <NumberFormat ss:Format="0%"/>
      </Style>
      <Style ss:ID="s30">
       <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
       <Borders>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
      </Style>
     </Styles>
     <Worksheet ss:Name="Sheet1">
      <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
       x:FullRows="1" ss:DefaultRowHeight="13.2">
       <Column ss:StyleID="s23" ss:AutoFitWidth="0" ss:Width="85.2" ss:Span="3"/>
       <Column ss:Index="5" ss:StyleID="s25" ss:AutoFitWidth="0" ss:Width="85.2"/>
       <Column ss:StyleID="s27" ss:AutoFitWidth="0" ss:Width="85.2"/>
       <Row>
        <Cell ss:StyleID="s22"><Data ss:Type="String">Order ID</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="String">Product ID</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="String">Unit Price</Data></Cell>
        <Cell ss:StyleID="s22"><Data ss:Type="String">Quantity</Data></Cell>
        <Cell ss:StyleID="s24"><Data ss:Type="String">Discount</Data></Cell>
        <Cell ss:StyleID="s26"><Data ss:Type="String">Total</Data></Cell>
       </Row>
    <xsl:for-each select="xml/rs:data/z:row">
       <Row ss:AutoFitHeight="0" ss:Height="13.5">
        <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
        <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
       </Row>
    </xsl:for-each>
       <Row ss:AutoFitHeight="0" ss:Height="25.049999999999997">
        <Cell ss:StyleID="s28"/>
        <Cell ss:StyleID="s28"/>
        <Cell ss:StyleID="s28"/>
        <Cell ss:StyleID="s28"/>
        <Cell ss:StyleID="s29"/>
        <Cell ss:StyleID="s30" ss:Formula="=SUM(R2C:R[-1]C)"><Data ss:Type="Number">73926</Data></Cell>
       </Row>
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <Print>
        <ValidPrinterInfo/>
        <HorizontalResolution>600</HorizontalResolution>
        <VerticalResolution>600</VerticalResolution>
       </Print>
       <Selected/>
       <DoNotDisplayGridlines/>
       <FreezePanes/>
       <FrozenNoSplit/>
       <SplitHorizontal>1</SplitHorizontal>
       <TopRowBottomPane>1</TopRowBottomPane>
       <ActivePane>2</ActivePane>
       <Panes>
        <Pane>
         <Number>3</Number>
        </Pane>
        <Pane>
         <Number>2</Number>
         <ActiveRow>0</ActiveRow>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
     <Worksheet ss:Name="Sheet2">
      <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
       x:FullRows="1" ss:DefaultRowHeight="13.2"/>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
     <Worksheet ss:Name="Sheet3">
      <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
       x:FullRows="1" ss:DefaultRowHeight="13.2"/>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>
    </xsl:template>
    </xsl:stylesheet>
    }'
    ;
                 
      --            
      myParser := DBMS_XMLPARSER.newParser;
      DBMS_XMLPARSER.parseBuffer(myParser, indoc);
      indomdoc   := DBMS_XMLPARSER.getDocument(myParser);
      DBMS_XMLPARSER.parseBuffer(myParser, xsldoc);
      xsltdomdoc := DBMS_XMLPARSER.getDocument(myParser);
      xsl        := DBMS_XSLPROCESSOR.newStyleSheet(xsltdomdoc, '');
      proc       := DBMS_XSLPROCESSOR.newProcessor;
      --apply stylesheet to DOM document  
      outdomdocf := DBMS_XSLPROCESSOR.processXSL(proc, xsl, indomdoc);
      outnode    := DBMS_XMLDOM.makeNode(outdomdocf);
      -- PL/SQL DOM API for XMLType can be used here
      DBMS_XMLDOM.writeToBuffer(outnode, buf);
      DBMS_OUTPUT.put_line(buf);
      DBMS_XMLDOM.freeDocument(indomdoc);
      DBMS_XMLDOM.freeDocument(xsltdomdoc);
      DBMS_XMLDOM.freeDocFrag(outdomdocf);
      DBMS_XMLPARSER.freeParser(myParser);
      DBMS_XSLPROCESSOR.freeProcessor(proc);
      DBMS_XMLGEN.CLOSECONTEXT (h_xmlgen);
     
    END;

     
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx Sergey.,
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can any 1 simply explain how the keywords "xslprocessor.selectnodes", "xmldom.item", & "xslprocessor.valueof" works ???
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation