OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to do XML based data manipulation in SQL and XSLT

April 23rd, 2012 By Frank Zhou

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or the id attribute is 3.

2) Remove the <MiddleName> tag if this is empty.

3) Transform all the xml attributes to elements.

CREATE TABLE PERSON
(ID            NUMBER,
 FIRSTNAME     VARCHAR2(20 BYTE),
 MIDDLENAME    VARCHAR2(20 BYTE),
 LASTNAME      VARCHAR2(20 BYTE),
 LOCATION_ID   NUMBER);

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (1, 'Frank', null, 'Zhou', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (2, 'Peter', 'B',  'Lee', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (3, 'John' , 'C',   'Adam', 2);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (4, 'Dave' , null, 'Washington', 2);

CREATE TABLE LOCATION
(LOCATION_ID       NUMBER,
 LOCATION_NAME     VARCHAR2(20 BYTE)
 );

INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (1, 'BOSTON');
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (2, 'NEW YORK');
COMMIT;

---------------Input XML Data is generated by the top part of the query (WITH DATA AS)-----------------

<ROWSET>
  <ROW ID="1" Location_name="BOSTON">
    <FirstName>Frank</FirstName>
    <MiddleName/>
    <LastName>Zhou</LastName>
  </ROW>
  <ROW ID="2" Location_name="BOSTON">
    <FirstName>Peter</FirstName>
    <MiddleName>B</MiddleName>
    <LastName>Lee</LastName>
  </ROW>
  <ROW ID="3" Location_name="NEW YORK">
    <FirstName>John</FirstName>
    <MiddleName>C</MiddleName>
    <LastName>Adam</LastName>
  </ROW>
  <ROW ID="4" Location_name="NEW YORK">
    <FirstName>Dave</FirstName>
    <MiddleName/>
    <LastName>Washington</LastName>
  </ROW>
</ROWSET>

---------------------------------------------------SQL Query------------------------------------

WITH DATA AS
(SELECT XMLPARSE(DOCUMENT XMLSERIALIZE(CONTENT   
           XMLELEMENT(ROWSET, 
               XMLAGG(XMLELEMENT("ROW",  
                      XMLATTRIBUTES(ID AS "ID",  LOCATION_NAME AS "Location_name"),
                      XMLELEMENT("FirstName",    FIRSTNAME), 
                      XMLELEMENT("MiddleName",   MIDDLENAME),
                      XMLELEMENT( "LastName",    LASTNAME  )
                      )  ORDER BY ID)
                 )  )  ) AS XML
 FROM  person p, location l
 WHERE p.location_id = L.LOCATION_ID
)
SELECT  XMLSERIALIZE(DOCUMENT XMLTRANSFORM(   
        (SELECT XML FROM DATA) ,
 XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:strip-space elements="*"/>
 <xsl:output indent="yes"/>
 <xsl:template match="node()|@*">
        <xsl:copy><xsl:apply-templates select="node()|@*"/></xsl:copy>
 </xsl:template>
 <xsl:template match="ROW[position() = last() or @ID=3 ]/FirstName ">
        <FN><xsl:apply-templates select="node()|@*"/></FN>
 </xsl:template>
 <xsl:template match="ROW[not(MiddleName/text())]/MiddleName"/> 
  <xsl:template match="@*">
        <xsl:element name="{name(.)}"><xsl:value-of select="."/></xsl:element>
  </xsl:template>
  </xsl:stylesheet>')))  AS XML_DATA
 FROM DUAL;

---------------------------------Output XML Data ---------------------------------------

<?xml version="1.0" encoding="utf-8"?>

<ROWSET>
 <ROW>
  <ID>1</ID>
  <Location_name>BOSTON</Location_name>
  <FirstName>Frank</FirstName>
  <LastName>Zhou</LastName>
 </ROW>
 <ROW>
  <ID>2</ID>
  <Location_name>BOSTON</Location_name>
  <FirstName>Peter</FirstName>
  <MiddleName>B</MiddleName>
  <LastName>Lee</LastName>
 </ROW>
 <ROW>
  <ID>3</ID>
  <Location_name>NEW YORK</Location_name>
  <FN>John</FN>
  <MiddleName>C</MiddleName>
  <LastName>Adam</LastName>
 </ROW>
 <ROW>
  <ID>4</ID>
  <Location_name>NEW YORK</Location_name>
  <FN>Dave</FN>
  <LastName>Washington</LastName>
 </ROW>
</ROWSET>

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question