Archive

Archive for the ‘XSLT’ Category

How To : Flatten out a Recursive XML Document

January 17, 2017 Leave a comment

I blogged a few times about XML flattening in the past :

Those posts were about converting any XML structure and data into a schema-less model called “Edge”.
What I’ll be presenting here applies to XML documents having a recursive structure,
e.g.

<emp id="1">
  <emp id="2"/>
  <emp id="3">
    <emp id="4"/>
    <emp id="5"/>
  </emp>
</emp>

which could be converted to an adjency list model :

ID  PARENT_ID
--- ---------
  1
  2         1
  3         1
  4         3
  5         3

or, denormalized into level-based columns, like this :

ID_1 ID_2 ID_3
---- ---- ----
   1    2
   1    3    4
   1    3    5

 

Read more…

XMLTransform Tips and Tricks

November 16, 2014 4 comments

No real new stuff there but as I’m a big user of XSLT transformation inside the database, I just thought I could share a few interesting (hidden) things I’ve encountered so far, that is :

  1. Using XSLT parameters with XMLTransform
  2. Using text output method with XMLTransform and XMLType.transform method

 

Read more…

XMLTABLE vs. External XSLT preprocessor

September 16, 2013 2 comments

We all know – I hope ;) – about XMLTABLE function to shred an XML document into relational rows and columns. In this article, I will describe an alternative approach to achieve the same result with an external table and the preprocessor feature.
And in case you wonder, no, it’s not one of this attempt to implement an XML parser using SQL*Loader syntax, something we can see from time to time as an answer to “How can I load my XML file using SQL*Loader?”.
This method relies on the external table preprocessor to transform the input XML file into CSV format and pipe the result to the loader.

 

Read more…

How To : Access database tables from XSLT context

July 30, 2012 12 comments

In this post we’ll see how to access a database table (or view) in the context of an XSL transformation.

The key feature allowing that is the ability to query a table using a DBUri URI.
In SQL or PL/SQL, DBUris are managed via the DBUriType object and its methods (see DBUris: Pointers to Database Data), and over HTTP protocol via the DBUriServlet.
The URI address is an XPath-based string, just like the oradb scheme available in XQuery.

In XSLT, we can pass a DBUri URI to the document() function in order to retrieve data in XML format. There are however some restrictions.

Read more…

Categories: HowTo, XSLT Tags: , , ,

How To : Flatten out an XML Hierarchical Structure

December 13, 2011 7 comments

Besides the usual requirement to present XML data in a relational way, sometimes we may also need to flatten the whole XML structure and build parent-child relationships from it.
For instance, converting this :

<root>
 <item>ABC<item>
 <item/>
 <item>
   <subitem>XYZ</subitem>
 </item>
<root>

to

    TAG_ID TAG_NAME        TAG_VALUE          PARENT_TAG_ID
---------- --------------- ---------------- ---------------
         1 root
         2 item            ABC                            1
         3 item                                           1
         4 item                                           1
         5 subitem         XYZ                            4

Read more…

How To : Generate a recursive XML structure

November 24, 2011 Leave a comment

Here are three techniques to generate a recursive XML structure, e.g. :

<employee id="100" name="King">
  <employee id="101" name="Kochhar">
    <employee id="108" name="Greenberg">
      <employee id="109" name="Faviet"/>
      <employee id="110" name="Chen"/>
      <employee id="111" name="Sciarra"/>
      <employee id="112" name="Urman"/>
      <employee id="113" name="Popp"/>
    </employee>
    <employee id="200" name="Whalen"/>
    <employee id="203" name="Mavris"/>
    <employee id="204" name="Baer"/>
    <employee id="205" name="Higgins">
      <employee id="206" name="Gietz"/>
    </employee>
  </employee>

  ...

  <employee id="201" name="Hartstein">
    <employee id="202" name="Fay"/>
  </employee>
</employee>

Read more…

Categories: HowTo, XQuery, XSLT Tags: , ,