Concatinating XML data in sequence and then use an XPath to get out data

Question

First off, my tables look like this..

Checkpoint_Data
---------------
Checkpoint_Id
Sequence_Number
Checkpoint_Data

Checkpoint
----------
Checkpoint_Id
Location
Transaction_Id

Transaction
-----------
Transaction_Id
Name
Time

Checkpoint_data contains broken up XML, and the sequence number determines how it fits together

I want to concatenate all of a Checkpoint's linked Checkpoint_Data together in sequence (1,2,..) and then use an XPath to extract some data that I want.

Here is the query I have right now, but it does not do any concatenation, and does not work correctly:

SELECT distinct(xmltype(data.trans_data).extract('//somePath/text()').getStringVal())  
FROM Checkpoint_Data cpd, Checkpoint cp, Transaction tr
WHERE cpd.checkpoint_id = cp.checkpoint_id
     and cp.location = 'someLocation'
     and cp.trans_id = tr.trans_id
     and tr.time >= to_date('3/26/2013:8:00 AM', 'mm/dd/yyyy:hh:miam')  
     and tr.time <= to_date('3/26/2013:11:00 AM', 'mm/dd/yyyy:hh:miam') 

How can I concatenate the broken up XML contained in Checkpoint_Data and then use an XPath to extract data from it?

Answer 1

PLSQL approach:

SQL> create or replace function get_xml_doc(p_id Checkpoint_Data.checkpoint_id%type)
  2    return xmltype deterministic
  3  is
  4    v_xml clob;
  5  begin
  6    for r_row in (select cd.checkpoint_data
  7                    from Checkpoint_Data cd
  8                   where cd.checkpoint_id = p_id
  9                   order by cd.sequence_number)
 10    loop
 11      v_xml := v_xml || r_row.checkpoint_data;
 12    end loop;
 13
 14    return xmltype(v_xml);
 15  end get_xml_doc;
 16  /

Function created.

SQL>
SQL> select cp.checkpoint_id, get_xml_doc(cp.checkpoint_id) xml_doc
  2    from checkpoint cp, transaction tr
  3   where cp.location = 'someLocation'
  4     and cp.trans_id = tr.trans_id
  5     and tr.time <= sysdate
  6   group by cp.checkpoint_id;

CHECKPOINT_ID XML_DOC
------------- ----------------------------------------
            1 <root>
                <a>1st node</a>
                <b>2nd node</b>
                <compType>
                  <a>foo</a>
                </compType>
                <d>another node</d>
              </root>

or just SQL if PLSQL isn't an option:

you can use CDATA with XMLAGG eg :

SQL> select * from Checkpoint_Data;

CHECKPOINT_ID SEQUENCE_NUMBER CHECKPOINT_DATA
------------- --------------- --------------------------------------------------
            1               1 <root><a>1st node</a><b>2nd node</b>
            1               2 <compType><a>foo</a></compType><d>another node</d>
            1               3 </root>


SQL> select c.xmlstr.transform(xmltype('<xsl:stylesheet version="1.0"
  2          xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  3          <xsl:output method="xml" omit-xml-declaration = "yes" version="1.0" />
  4          <xsl:template match="/">
  5              <xsl:for-each select="/root/nde">
  6                  <xsl:sort select="@seq"/>
  7                  <xsl:value-of select="." disable-output-escaping="yes" />
  8              </xsl:for-each>
  9          </xsl:template>
 10      </xsl:stylesheet>')) xml
 11    from (SELECT cpd.checkpoint_id,xmlelement("root", xmlagg(
 12                 xmltype('<nde seq="' || cpd.sequence_number || '"><![CDATA['||cpd.checkpoint_data||']]></nde>'))) xmlstr
 13          FROM Checkpoint_Data cpd, Checkpoint cp, Transaction tr
 14          WHERE cpd.checkpoint_id = cp.checkpoint_id
 15               and cp.location = 'someLocation'
 16               and cp.trans_id = tr.trans_id
 17               and tr.time <= sysdate
 18           group by cpd.checkpoint_id) c;

XML
--------------------------------------------------------------------------------
<root><a>1st node</a><b>2nd node</b><compType><a>foo</a></compType><d>another no
de</d></root>

so i took each xml fragment and converted it to a CDATA node like:

<node seq="1"><!CDATA[<root><a>1st node</a><b>2nd node</b>]]></node>

then used XMLAGG to get these into one document like

<root>
  <node seq="1"><!CDATA[<root><a>1st node</a><b>2nd node</b>]]></node>
  <node seq="3"><!CDATA[</root>]]></node>
  <node seq="2"><!CDATA[<compType><a>foo</a></compType><d>another node</d>]]></node>
</root>

note: the sequences may be jumbed by the group by..so I applied an XSL to order them and print them (you could write your own aggregate to do this in order if you wanted or just write a pl/sql routine to get all the fragments and append them for you in order, if that's an option, you may prefer that).

NOTE: in 10g oracle seems to ignore disable-output-escaping (its ok in 11g). if you use 10g you can add a call to dbms_xmlgen.convert(clob, 1) to decode it.

select dbms_xmlgen.convert(
         xmlserialize(
           content c.xmlstr.transform(xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
    <xsl:stylesheet version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output method="xml" version="1.0" omit-xml-declaration = "yes"  encoding="UTF-8" indent="no"/>
        <xsl:template match="/">
            <xsl:for-each select="/root/node">
                <xsl:sort select="@seq"/>
                <xsl:value-of select="." disable-output-escaping="yes"/>
            </xsl:for-each>
        </xsl:template>
    </xsl:stylesheet>')) as clob), 1) xsl, xmlstr
  from (SELECT cpd.checkpoint_id,xmlelement("root", xmlagg(
               xmltype('<node seq="' || cpd.sequence_number || '"><![CDATA['||cpd.checkpoint_data||']]></node>'))) xmlstr
        FROM Checkpoint_Data cpd, Checkpoint cp, Transaction tr
        WHERE cpd.checkpoint_id = cp.checkpoint_id
             and cp.location = 'someLocation'
             and cp.trans_id = tr.trans_id
             and tr.time <= sysdate
         group by cpd.checkpoint_id) c;

fiddle: http://sqlfiddle.com/#!4/e1bf8/1