Home » SQL & PL/SQL » SQL & PL/SQL » xml converting task (11g)  () 1 Vote
xml converting task [message #687502] Wed, 22 March 2023 02:58 Go to next message
urmas19
Messages: 7
Registered: March 2023
Junior Member
There is
myXml := XMLType('
<container>
  <r>
    <c>c11</c>
    <c>c12</c>
    <c>c13</c>
  </r>
  <r>
    <c>c21</c>
    <c>c22</c>
    <c>c23</c>
  </r>
</container>
');
1). How to output selection like this:
C1    C2    C3
----- ----- -----
c11   c12   c13
c21   c22   c23
2). How to transform myXml into:
myXml1 := XMLType('
<container>
  <str r="1" c="1">c11</str>
  <str r="1" c="2">c12</str>
  <str r="1" c="3">c13</str>
  <str r="2" c="1">c21</str>
  <str r="2" c="2">c22</str>
  <str r="2" c="3">c23</str>
</container>
');
Notine: realize it without XSLT-transformation. Amounts of "r" and "c" can be variable.

3). Realise result "2)" with XSLT-transformation.

Thank you very much!

[moderator note(bb): added code tags for formatting; next time please add them yourself]

[Updated on: Wed, 22 March 2023 03:39] by Moderator

Report message to a moderator

Re: xml converting task [message #687504 is a reply to message #687502] Wed, 22 March 2023 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68512
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It appears that you are in Russia. In that case, using Oracle products would be illegal. Can you confirm that you are in compliance with the Oracle license agreement?

Re: xml converting task [message #687505 is a reply to message #687504] Wed, 22 March 2023 04:08 Go to previous messageGo to next message
urmas19
Messages: 7
Registered: March 2023
Junior Member
I'm sorry, I didn't know about this one - about that using Oracle software is illegal in Russia now. Sad
I have old version (Personal Edition) on my laptop. Is it illegal too?
Re: xml converting task [message #687506 is a reply to message #687505] Wed, 22 March 2023 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68512
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know but as a 11g PE is so old and your question may be useful for others so I decide to nevertheless provide a solution for point 2:
SQL> with data as (
  2    select XMLType('
  3  <container>
  4    <r>
  5      <c>c11</c>
  6      <c>c12</c>
  7      <c>c13</c>
  8    </r>
  9    <r>
 10      <c>c21</c>
 11      <c>c22</c>
 12      <c>c23</c>
 13    </r>
 14  </container>') data
 15   from dual)
 16  select xmlserialize(
 17           document xmlelement("container",
 18                      xmlagg(
 19                        xmlelement("str",
 20                                   xmlattributes(x.position as "r", y.position as "c"),
 21                                   y.c)
 22                     )      )
 23           indent size=2) res
 24  from data,
 25       xmltable('/container/r' passing data
 26         columns
 27           position for ordinality,
 28           r xmltype path '/'
 29       ) x,
 30       xmltable ('/r/c' passing x.r
 31         columns
 32           position for ordinality,
 33           c varchar2(10) path '/'
 34       ) y
 35  /
RES
--------------------------------------------------------------------------------------------
<container>
  <str r="1" c="1">c11</str>
  <str r="1" c="2">c12</str>
  <str r="1" c="3">c13</str>
  <str r="2" c="1">c21</str>
  <str r="2" c="2">c22</str>
  <str r="2" c="3">c23</str>
</container>
Re: xml converting task [message #687507 is a reply to message #687502] Wed, 22 March 2023 04:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
Senior Member
1)

SCOTT@orcl_12.1.0.2.0> -- data you provided:
SCOTT@orcl_12.1.0.2.0> WITH
  2    xml_tab AS
  3  	 (SELECT XMLType('
  4  <container>
  5    <r>
  6  	 <c>c11</c>
  7  	 <c>c12</c>
  8  	 <c>c13</c>
  9    </r>
 10    <r>
 11  	 <c>c21</c>
 12  	 <c>c22</c>
 13  	 <c>c23</c>
 14    </r>
 15  </container>
 16  ') AS myXml
 17  	 FROM	DUAL)
 18  -- query:
 19  SELECT x.c1, x.c2, x.c3
 20  FROM   xml_tab t,
 21  	    XMLTABLE
 22  	      ('/container/r'
 23  	       PASSING t.myXml
 24  	       COLUMNS
 25  		 row_num for ordinality,
 26  		 c1  VARCHAR2(3)  PATH 'c[1]',
 27  		 c2  VARCHAR2(3)  PATH 'c[2]',
 28  		 c3  VARCHAR2(3)  PATH 'c[3]') x
 29  /

C1  C2  C3
--- --- ---
c11 c12 c13
c21 c22 c23

2 rows selected.
Re: xml converting task [message #687508 is a reply to message #687504] Wed, 22 March 2023 05:52 Go to previous messageGo to next message
urmas19
Messages: 7
Registered: March 2023
Junior Member
Yes, I confirm this one.
Barbara, Michel, thank you very much for your help!
Re: xml converting task [message #687509 is a reply to message #687508] Wed, 22 March 2023 14:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3254
Registered: January 2010
Location: Connecticut, USA
Senior Member
with t as (
           select xmltype('
<container>
  <r>
    <c>c11</c>
    <c>c12</c>
    <c>c13</c>
  </r>
  <r>
    <c>c21</c>
    <c>c22</c>
    <c>c23</c>
  </r>
</container>
') xmldoc from dual
         )
select  xmlserialize(
                     content
                     xmlquery(
                              '<container>
                               {
                                for $x at $r in $d/container/r
                                  for $y at $c in $x/c/text()
                                    return <str r="{$r}" c="{$c}">{$y}</str>
                               }
                               </container>'
                              passing xmldoc as "d"
                              returning content
                             )
                    indent size = 2
                   ) new_xmldoc
  from  t
/
NEW_XMLDOC
-----------------------------
<container>
  <str r="1" c="1">c11</str>
  <str r="1" c="2">c12</str>
  <str r="1" c="3">c13</str>
  <str r="2" c="1">c21</str>
  <str r="2" c="2">c22</str>
  <str r="2" c="3">c23</str>
</container>


SQL>
SY.
Re: xml converting task [message #687515 is a reply to message #687509] Thu, 23 March 2023 14:11 Go to previous message
mathguy
Messages: 79
Registered: January 2023
Member
The XSLT transformation itself (using XMLTRANSFORM) is, of course, trivial. The trick is to create the style sheet, which - strictly speaking - is not a SQL question.

I offer the solution below only as practice for myself. I had forgotten everything I had read about XSLT (even though it was only a year ago), so this was fun. Take it with a grain of salt; who knows how many mistakes or inefficient things are hiding here.

In the first subquery in the WITH clause I created four different documents, to test that the style sheet works as expected on special cases (for example, on an empty container, or on one where some of the cells are empty, etc.)

The XMLSERIALIZE wrappers shouldn't be needed; I have them only so that we can inspect the inputs and outputs with human eyes. To a computer they may only get in the way.

/*
// A few XML documents to test on
*/
with t as (
select 181 as id, xmltype('
             <container>
                 <r> <c>c11</c> <c>c12</c> <c>c13</c> </r>
                 <r> <c>c21</c> <c>c22</c> <c>c23</c> </r>
             </container>
             ') as xmldoc from dual union all
select 105, xmltype('<container/>') from dual union all
select 402, xmltype('
             <container>
               <r> <c>something</c> </r>
               <r> <c/> </r>
             </container>
             ') from dual union all
select 132, xmltype('
             <container>
               <r> <c>nothing here</c> </r>
             </container>
             ') from dual
  )
/*
// Style sheet definition
*/
, s as (
select xmltype('
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

  <xsl:template match="/">
    <xsl:apply-templates select="container"/>
  </xsl:template>
  
  <xsl:template match="container">
      <xsl:element name="container">
        <xsl:apply-templates select="r"/>
      </xsl:element>
  </xsl:template>
  
  <xsl:template match="r">
    <xsl:apply-templates select="c">
      <xsl:with-param name="row-number" select="position()"/>
    </xsl:apply-templates>
  </xsl:template>
  
  <xsl:template match="c">
    <xsl:param name="row-number"/>
    <xsl:element name="str">
      <xsl:attribute name="r">
        <xsl:value-of select="$row-number"/>
      </xsl:attribute>
      <xsl:attribute name="c">
        <xsl:value-of select="position()"/>
      </xsl:attribute>
      <xsl:value-of select="."/>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>
') as style from dual)
/*
// Query
*/
select t.id,
       xmlserialize(document t.xmldoc                        indent) as xmldoc,
       xmlserialize(document xmltransform(t.xmldoc, s.style) indent) as new_xmldoc
from   t cross join s
;



OUTPUT:



  ID XMLDOC                         NEW_XMLDOC                            
---- ------------------------------ --------------------------------------
 181 <container>                    <container>                           
       <r>                            <str r="1" c="1">c11</str>          
         <c>c11</c>                   <str r="1" c="2">c12</str>          
         <c>c12</c>                   <str r="1" c="3">c13</str>          
         <c>c13</c>                   <str r="2" c="1">c21</str>          
       </r>                           <str r="2" c="2">c22</str>          
       <r>                            <str r="2" c="3">c23</str>          
         <c>c21</c>                 </container>                          
         <c>c22</c>                                                       
         <c>c23</c>                                                       
       </r>                                                               
     </container>                                                         

 105 <container/>                   <container/>                          

 402 <container>                    <container>                           
       <r>                            <str r="1" c="1">something</str>    
         <c>something</c>             <str r="2" c="1"/>                  
       </r>                         </container>                          
       <r>                                                                
         <c/>                                                             
       </r>                                                               
     </container>                                                         

 132 <container>                    <container>                           
       <r>                            <str r="1" c="1">nothing here</str> 
         <c>nothing here</c>        </container>                          
       </r>                                                               
     </container>                                                         
Previous Topic: convert xml column value into columns
Next Topic: Min and Max values of contiguous rows (3 merged)
Goto Forum:
  


Current Time: Wed Oct 04 22:46:45 CDT 2023