Hi All,
I have not dealt much with PL/SQL and XML combination so excuse me with my ignorance.
I am trying to generate a multiple node XML file using DBMS_XMGEN using views in my query. My goal is to generate a XML file in the below format.
<?xml version="1.0" encoding="UTF-8"?>
<AES xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="TEST_Schema.xsd">
<Transaction>
<PartnerID Value="123456"/>
<Sender Value="Sender"/>
<Recipient Value="Recipient"/>
</Transaction>
<Main>
<Header>
<OrderNumShip>1234567</OrderNumShip>
<ShipDate>01/01/2001</ShipDate>
</Header>
<Parties>
<Party PartyQualifier="Seller">
<AddressLine1>address1</AddressLine1>
<City>City</City>
<State>TX</State>
</Party>
<Party PartyQualifier="ShipFrom">
<AddressLine1>address1</AddressLine1>
<City>City</City>
<State>TX</State>
</Party>
</Parties>
<DetailConf>
<Detail>
<LineNum>1</LineNum>
<Product>11111</Product>
<OrderNumShip>1234567</OrderNumShip>
</Detail>
<Detail>
<LineNum>2</LineNum>
<Product>22222</Product>
<OrderNumShip>1234567</OrderNumShip>
</Detail>
</DetailConf>
<EquipConf>
<Equip>
<NetWeight>3</NetWeight>
<Length>1</Length>
</Equip>
</EquipConf>
</Main>
</AES>
In the above XML I include only few attributes, but each of these nodes (ROWSETs) have a large number attributes; so I created a views to fetch the attributes for each of the nodes and tried the below. I was able some of the output of the above but because of the query it is performing very bad and also I was not able to use custom tags for all the rowsets.
DECLARE
l_xmltype XMLTYPE;
l_ctx dbms_xmlgen.ctxhandle;
BEGIN
l_ctx := dbms_xmlgen.newcontext('SELECT H.*,
CURSOR(SELECT D.*
FROM XXIP_SHIPMENT_DETAILS_V D
WHERE d.OrderNumShip = h.OrderNumShip) DetailConf,
CURSOR(SELECT E.*
FROM XXIP_SHIPMENT_EQUIP_V E) EquipConf
FROM XXIP_SHIPMENT_HEADERS_V H
WHERE H.OrderNumShip = 1234567'
);
dbms_xmlgen.setrowsettag(l_ctx, 'Main');
dbms_xmlgen.setrowtag(l_ctx, 'HEADER');
l_xmltype := dbms_xmlgen.getXmlType(l_ctx);
dbms_xmlgen.closeContext(l_ctx);
dbms_output.put_line(l_xmltype.getClobVal);
END;
I would like to know if there is a better way to approach this problem and if there is a different API I should be using other than the DBMS_XMLGEN.
Thanks in advance.
Regards