3 Replies Latest reply: Mar 14, 2013 4:43 AM by odie_63 RSS

    Xpath question

    ascheffer
      If have a XPATH question
      I have this
      declare
          t_ns varchar2(200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
          t_nd dbms_xmldom.domnode;
          t_nl dbms_xmldom.domnodelist;
      begin
          t_nd := dbms_xmldom.makenode( dbms_xmldom.getdocumentelement( dbms_xmldom.newdomdocument( xmltype( 
      '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"  count="8" uniqueCount="4">
      <si>
        <r><t>United</t></r>
        <r><t>States</t></r>
      </si>
      <si><t>Seattle</t></si>
      <si><t>Denver</t><t>Anton</t></si>
      <si><t>New York</t></si>
      </sst>' ) ) ) );
            t_nl := dbms_xslprocessor.selectnodes( t_nd, '/sst/si', t_ns );
            for i in 0 .. dbms_xmldom.getlength( t_nl ) - 1
            loop
      dbms_output.put_line( i || ' >' || dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), './/t/text()' ) || '<' );
            end loop;
      end;
      /
      0 >United<
      1 >Seattle<
      2 >Denver<
      3 >New York<
      But I need the output to be
      0 >UnitedStates<
      1 >Seattle<
      2 >Denver<
      3 >New York<
      Can anybody help me out?
        • 1. Re: Xpath question
          mdrake
          Without all the tedious DOM Programming I get this
          SQL> column value format A20
          SQL> VAR XML CLOB
          SQL> --
          SQL> begin
            2    :XML :=
            3    '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"  count="8" uniqueCount="4">
            4      <si>
            5         <r>
            6           <t>United</t>
            7         </r>
            8         <r>
            9           <t>States</t>
           10        </r>
           11       </si>
           12       <si>
           13         <t>Seattle</t>
           14       </si>
           15       <si>
           16         <t>Denver</t>
           17         <t>Anton</t>
           18       </si>
           19      <si>
           20        <t>New York</t>
           21      </si>
           22  </sst>';
           23  end;
           24  /
          
          PL/SQL procedure successfully completed.
          
          SQL> select *
            2    from XMLTABLE(
            3          xmlnamespaces(
            4            default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
            5          ),
            6          '/sst/si'
            7          passing XMLTYPE(:XML)
            8          columns
            9            IND   for ordinality,
           10            VALUE varchar2(400) path '.'
           11          )
           12  /
          
                 IND VALUE
          ---------- --------------------
                   1 UnitedStates
                   2 Seattle
                   3 DenverAnton
                   4 New York
          
          SQL>
          So basically get the set of si elements.

          For each si element use the '.' operator to get the concatentated value of the text nodes..

          If you need to insert spaces then you'll need to do some XQuery programming that looks at the number of <t> and <r> tags in each <si> tag...

          I'm not quite sure I understand the logic for that.. it appears to be something like if <si> contains <r> then for each t/text() concatentate the values with a space delimiter. If <si> contains <t> then take the value of the <r>[1]/text()...

          Edited by: mdrake on Mar 13, 2013 5:24 PM
          • 2. Re: Xpath question
            ascheffer
            That works also using DOM
            declare
                t_ns varchar2(200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
                t_nd dbms_xmldom.domnode;
                t_nl dbms_xmldom.domnodelist;
            begin
                t_nd := dbms_xmldom.makenode( dbms_xmldom.getdocumentelement( dbms_xmldom.newdomdocument( xmltype( 
            '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"  count="8" uniqueCount="4">
            <si><r><t xml:space="preserve">United </t></r><r><rPr><sz val="11"/><color rgb="FFFF0000"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr><t>States</t></r></si>
            <si><t>Denver</t></si>
            <si><t>New York</t></si>
            </sst>' ) ) ) );
                  t_nl := dbms_xslprocessor.selectnodes( t_nd, '/sst/si', t_ns );
                  for i in 0 .. dbms_xmldom.getlength( t_nl ) - 1
                  loop
            dbms_output.put_line( i || '  ' || dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), '.' ) );
                  end loop;
            end;
            / 
            
            0 United States
            1 Denver
            2 New York
            But I do hope that Microsoft doensn't use more text nodes besides the <t> inside the <si> tag
            • 3. Re: Xpath question
              odie_63
              But I do hope that Microsoft doensn't use more text nodes besides the <t> inside the <si> tag
              According to the current schema, it should be OK :

              http://www.schemacentral.com/sc/ooxml/e-ssml_si-1.html