Topic
1 reply Latest Post - ‏2013-05-15T23:06:51Z by MatthiasNicola
amcintyre
amcintyre
21 Posts
ACCEPTED ANSWER

Pinned topic splitting long xml into short xml

‏2013-04-25T00:52:45Z |

The Cookbook is very correct on p116 where it says "indexed access and data retrieval is faster for smaller documents". Working recently with a small inline xml row of 250 bytes vs trying to do the same thing with rows that are 60x to 600x longer points this out very clearly.

Anyway, no problem and I'm now successfully splitting the long responses (from a remote server) into smaller ones for insert.

But I'm worried that I'm running through the data 2 or 3 times the way I have the SQL coded and since this runs a few million times a day... first the XMLTABLE to break it up then the XMLDOCUMENT to put it back... seems like it could be simpler...

any advice would be very appreciated...

https://gist.github.com/anonymous/5456745

and the Cookbook has been great, thanks!!

Updated on 2013-04-25T01:01:42Z at 2013-04-25T01:01:42Z by amcintyre
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: splitting long xml into short xml

    ‏2013-05-15T23:06:51Z  in response to amcintyre

    Well, thanks for your positive feedback on the DB2 pureXML Cookbook. Always nice to hear :-).

    I looked at the SQL code sample that you provided on gist, and here it is again:

    insert into tab
    with cte as
      (select *
       from xmltable(....  passing xmlparse(document cast(? as clob)) as ...   <====== parameter has very long xml I need to split
                     columns
                     c1 xml path ...
                     c2 xml path ...
                    )
      )
    (select xmldocument(xmlelement(name ... xmlelement(name "xx", c1) ...
    from cte
    fetch first 1 row only
    )
    union all
    (select xmldocument(c2)
    from cte
    );

    I'm not quite clear why you need the subselect "select xmldocument(xmlelement(name..." and why you need to create additional elements with the XMLELEMENT function and why the UNION ALL.

    Maybe there is something specific about your document structure and target table that is not obvious to me. If you post the details then maybe I can provide additional comments.

    For some simpler examples, see:

    (a) Figure 2 in http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2009_Issue3/Tips/index.html

    (b) Section 5.7 in the DB2 pureXML Cookbook ("SPLITTING LARGE XML DOCUMENTS INTO SMALLER DOCUMENTS")

    (c) Section 11.2.1 in the DB2 pureXML Cookbook

    Thanks,

    Matthias