Example: XMLSPLIT Usage Example - Advanced SQL Engine - Teradata Database

The example shows how to use XMLSPLIT.

Set up the staging table used to store the XML source document.

CREATE TABLE LargeXMLs(id INTEGER, lxml CLOB);

INSERT INTO LargeXMLs VALUES(1, '<?xml version="1.0" encoding="UTF-8"?>
<root>
<customers>
 <customer>
  <c_custkey>1805</c_custkey>
  <c_name>Customer#000001805</c_name>
  <c_address>ZERs4Cu5lQTYD</c_address>
  <c_nationkey>9</c_nationkey>
  <c_phone>19-679-706-1096</c_phone>
  <c_acctbal>-274.75</c_acctbal>
  <c_mktsegment>AUTOMOBILE</c_mktsegment>
  <c_comment>quickly unusual courts alongside of the furiously 
       pending requests thrash careful  even package</c_comment>
 </customer>
 <customer>
  <c_custkey>1806</c_custkey>
  <c_name>Customer#000001806</c_name>
  <c_address>BB6Vr7W rSIpWKp</c_address>
  <c_nationkey>9</c_nationkey>
  <c_phone>19-872-322-3433</c_phone>
  <c_acctbal>254.17</c_acctbal>
  <c_mktsegment>MACHINERY</c_mktsegment>
  <c_comment>ideas are blithely. ironic instructions wake quickly.
          quickly regular theodolites haggle blithely</c_comment>
 </customer>
 <customer>
  <c_custkey>1807</c_custkey>
  <c_name>Customer#000001805</c_name>
  <c_address>ZERs4Cu5lQTYD</c_address>
  <c_nationkey>9</c_nationkey>
  <c_phone>19-679-706-1096</c_phone>
  <c_acctbal>-274.75</c_acctbal>
  <c_mktsegment>AUTOMOBILE</c_mktsegment>
  <c_comment>quickly unusual courts alongside of the furiously 
       pending requests thrash careful  even package</c_comment>
 </customer>
</customers>
<dealers>
 <dealer>
  <d_id>12610</d_id >
  <d_name>VBIT</d_name>
  <d_address>HYD</d_address>
  <d_comment>AP Based dealer</d_comment>
 </dealer>
</dealers>
</root> ');

To split the XML document into smaller documents, run the following query with the XMLSPLIT function.

SELECT * FROM TABLE(XMLSPLIT(LargeXMLs.id, LargeXMLs.lxml, 400, '/root/customers/customer', '')) AS xs;

XMLDoc-1.xml
-------------------------
1 <?xml version="1.0" encoding="UTF-16"?> 
<root>
 <customers>
  <customer>
   <c_custkey>1805</c_custkey>
   <c_name>Customer#000001805</c_name>
   <c_address>ZERs4Cu5lQTYD</c_address>
   <c_nationkey>9</c_nationkey>
   <c_phone>19-679-706-1096</c_phone>
   <c_acctbal>-274.75</c_acctbal>
  </customer>
 </customers>
</root>

XMLDoc-2.xml
-------------------------
1 <?xml version="1.0" encoding="UTF-16"?>
<root>
 <customers>
  <customer>
   <c_custkey>1806</c_custkey>
   <c_name>Customer#000001806</c_name>
   <c_address>BB6Vr7W rSIpWKp</c_address>
   <c_nationkey>9</c_nationkey>
   <c_phone>19-872-322-3433</c_phone>
   <c_acctbal>254.17</c_acctbal>
  </customer>
 </customers>
</root>

XMLDoc-3.xml
-------------------------
1 <?xml version="1.0" encoding="UTF-16"?>
<root>
 <customers>
  <customer>
   <c_custkey>1807</c_custkey>
   <c_name>Customer#000001805</c_name>
   <c_address>ZERs4Cu5lQTYD</c_address>
   <c_nationkey>9</c_nationkey>
   <c_phone>19-679-706-1096</c_phone>
   <c_acctbal>-274.75</c_acctbal>
  </customer>
 </customers>
</root>

XMLDoc-4.xml
-------------------------
1 <?xml version="1.0" encoding="UTF-16"?>
<root>
 <dealers>
  <dealer>
   <d_id>12610</d_id>
   <d_name>VBIT</d_name>
   <d_address>HYD</d_address>
   <d_comment>AP Based dealer</d_comment>
  </dealer>
 </dealers>
</root>