| xml converting task [message #687502] |
Wed, 22 March 2023 02:58  |
 |
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>
');
C1 C2 C3
----- ----- -----
c11 c12 c13
c21 c22 c23
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>
');
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 #687506 is a reply to message #687505] |
Wed, 22 March 2023 04:56  |
 |
Michel Cadot
Messages: 68454
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 #687509 is a reply to message #687508] |
Wed, 22 March 2023 14:40 |
Solomon Yakobson
Messages: 3228
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>
|
|
|
|
| Re: xml converting task [message #687515 is a reply to message #687509] |
Thu, 23 March 2023 14:11 |
 |
mathguy
Messages: 54
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> |
|
|
|
) 1 Vote
] 
Search
Help
Members
Register
Login
Home
