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;
Result: The XML source document is split into smaller documents. Note: The output has been formatted for readability.
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>