Home » Developer & Programmer » JDeveloper, Java & XML » XML Parsing Query Problem
| XML Parsing Query Problem [message #336325] |
Fri, 25 July 2008 12:22  |
jweingarth
Messages: 1
Registered: July 2008
|
Junior Member |
|
|
I have the following XML that I am trying to part and insert into multiple tables.
I am unable to use the XMLSave as I need to insert columns that are not contained within the xml. My plan is to use Dynamic SQL.
I have never worked with XML's before, so I am new and not sure the best course of action. I will include what I done so far, but I had to start hard coding, so I am backing up for some recommendations.
'<shipment> <header> <shippingprofile>Oceanside</shippingprofile> <ordernumber>876397</ordernumber> <carrier>FEDEX_CA</carrier> <servicelevel>G</servicelevel> <shipvia>19</shipvia> <shipdate>2008-06-09</shipdate> <shipmentid>3981</shipmentid> <shiprate>58.16</shiprate> <shipaction>SHIP</shipaction> <paymenttype>PREPAID</paymenttype> <address type="shipto"> <attention>TYA</attention> <address1>19889 FRASERHIGHWAY</address1> <city>LANGLEY</city> <state>BC</state> <zipcode>V3A4E1</zipcode> <phone>949-858-5900</phone> <country>CA</country> </address> </header> <packages> <package id="1927"> <db_id>19101</db_id> <weight>59.2</weight> <length>57</length> <width>12</width> <height>16</height> <tracknum>362751270101825</tracknum> <rate>58.16</rate> <contents> <commodityitemcode>Catalytic Converters</commodityitemcode> <description>Automobile Parts</description> <customsvalue>310.95</customsvalue> <producercountry>US</producercountry> <destinationcountry>CA</destinationcountry> </contents><charges> <charge type="base" code="base">53.05</charge> <charge type="additional" code="fuel">2.91</charge> <charge type="special" code="residential">2.2</charge> </charges> <label> XkNDXl5YQV5NQ1leWFpeWEFeTVRUXk1EMTBeUE9OXkxIMCwwXlBSMTIsMTJeTU1UCl5GTzAsMTQ3XkdCODAwLDQsNF5GUwpeRk8wLDQwMV5HQjgwMCw0LDReRlMKXkZPMCw3N DZeR0I4MDAsNCw0XkZTCl5GTzM1LDEyXkFkTiwwLDBeRldOXkZIXkZERnJvbTpeRlMKXkZPMzUsMzFeQWROLDAsMF5GV05eRkheRkRNaWtlIFdyaWdodF5GUwpeRk8zNSw1MV 5BZE4sMCwwXkZXTl5GSF5GRExvZ2ljb3IgSW5jXkZTCl5GTzM1LDcxXkFkTiwwLDBeRldOXkZIXkZENDEgTW91aW50YWluIEF2ZV5GUwpeRk8zNSw5Ml5BZE4sMCwwXkZXTl5 GSF5GRF5GUwpeRk8zNSwxMTJeQWROLDAsMF5GV05eRkheRkRNQUxERU4sIE1BIDAyMTQ4XkZTCl5GTzM1LDEzMl5BZE4sMCwwXkZXTl5GSF5GRDQ4MC04NTctNzkwMF5GUwpe Rk80OTAsMzFeQWROLDAsMF5GV05eRkheRkRTaGlwIERhdGU6IDlKVU4yMDA4XkZTCl5GTzQ5MCw1MV5BZE4sMCwwXkZXTl5GSF5GREFjdHVhbCBXZ3Q6IDY2IExCXkZTCl5GT zQ5MCw3Ml5BZE4sMCwwXkZXTl5GSF5GRFN5c3RlbSM6IDAzNTUwMTgvRlhSUzA3NjReRlMKXkZPNDkwLDkxXkFkTiwwLDBeRldOXkZIXkZEQWNjb3VudDogUyAqKioqKioqKi ogXkZTCl5GTzEwLDE2Ml5BME4sMjAsMTheRldOXkZIXkZEVE9eRlMKXkZPNDMsMTU4XkEwTiwyNSwyN15GV05eRkheRkReRlMKXkZPNjAwLDE1Nl5BZE4sMCwwXkZXTl5GSF5 GRDk0OS04NTgtNTkwMF5GUwpeRk80MywxODZeQTBOLDI1LDI3XkZXTl5GSF5GRFRZQV5GUwpeRk80MywyMTZeQTBOLDI1LDI3XkZXTl5GSF5GRDE5ODg5IEZSQVNFUkhJR0hX QVleRlMKXkZPNDMsMjQ2XkEwTiwyNSwyN15GV05eRkheRkReRlMKXkZPNDMsMjc2XkEwTiwzMCwzMF5GV05eRkheRkRMQU5HTEVZLCBCQyBWM0E0RTFeRlMKXkZPNTMwLDMwN V5BME4sMzUsNDVeRldOXkZIXkZEKENBKV5GUwpeRk83MjUsMjMxXkFkTiwwLDBeRldOXkZIXkZER3JvdW5kXkZTCl5GTzY3MCwyNjheR0IxMDUsMTAsMTBeRlMKXkZPNjcwLD I3OF5HQjEwLDExMiwxMF5GUwpeRk83NjUsMjc4XkdCMTAsMTEyLDEwXkZTCl5GTzY3MCwzOTBeR0IxMDUsMTAsMTBeRlMKXkZPNjUwLDE4OF5BME4sNTAsNTVeRldOXkZIXkZ ERmVkRXheRlMKXkZPNjkwLDI4Nl5BME4sMTMwLDEzMF5GV05eRkheRkRHXkZTCl5GTzQ3NiwzXkdCNCwxNDUsNF5GUwpeRk8xMCwzNDheQTBOLDE1LDE1XkZXTl5GSF5GRFJl ZiA4NzYzOTdeRlMKXkZPNDA2LDM4OF5BME4sMTUsMTVeRldOXkZIXkZERGVwdCAxOTI3XkZTCl5GTzEwLDM2OF5BME4sMTUsMTVeRldOXkZIXkZESW52IF5GUwpeRk8xMCwzO DheQTBOLDE1LDE1XkZXTl5GSF5GRFBPIF5GUwpeRk84MCw3NzFeQlk0LDJeQkNOLDI5MCxOLE4sTixOXkZXTl5GRD47Pjg5NjEyMDI2MzYyNzUxMjcwMTAxODI1XkZTCl5GTz EzNSwxMDc4XkEwTiwyNSwyN15GV05eRkheRkQoOTYxMjAyNikgIDM2Mjc1MTIgIDcwMTAxODI1XkZTCl5GTzc4MywyODheQTBOLDE1LDE1XkZXQl5GSF5GRENMUzA2MTMwNS8 xMC8xNV5GUwpeRk8yNSwxMTAzXkEwTiw1MCw1NV5GV05eRkheRkRJTlQtR05EXkZTCl5GTzI1LDExNTBeQTBOLDM1LDQ1XkZXTl5GSF5GRFByZXBhaWReRlMKXkZPMzAwLDEx MTVeQTBOLDM1LDQ1XkZXTl5GSF5GRF5GUwpeRk8zMDAsMTE0OV5BME4sMzUsNDVeRldOXkZIXkZEXkZTCl5GTzMwLDExOTJeQTBOLDIwLDE4XkZXTl5GSF5GRFNFRDpORFIgM zAuNTheRlMKXkZPMzAsNDI4XkJZMiwyXkI3TiwxMCw1LDEyXkZIXkZXTl5GSF5GRFspPl8xRTAxXzFEMDJWM0E0RTFfMUQxMjQ2MzI2XzFEMzYyNzUxMjcwMTAxODI1XzFERk RFQl8xRDM2Mjc1MTJfMUQxNjFfMUQgXzFEMS8xXzFENjBMQl8xRE5fMUQxOTg4OSBGUkFTRVJISUdIV0FZXzFETEFOR0xFWV8xREJDXzFEVFlBXzFFMDZfMUQxMFpHSTAwNF8 xRDEyWjk0OTg1ODU5MDBfMUQyM1pZXzFEMjJaIF8xQ05fMUQyMFowLjAwXzFDMF8xRDlLODc2Mzk3XzFEOTlaR0lOVDAxXzFDODQwXzFDMzEwXzFDQ0FEXzFDQXV0b21vYmls ZSBQYXJ0c18xQ05EUiAzMC41OF8xQ18xRDI2WmU1Y2NfMUNfMURfMUVfMDReRlMKXkZPMDYxMCwxMjE1XkEwTiwxNSwxNV5GRF5GUwpeU
FExLDAwMDAsMDAwMCxOXlhaCg==</label> </package> </packages></shipment>';
CREATE OR REPLACE PROCEDURE xml_main
IS
p xmlparser.parser;
doc CLOB;
v_xmldoc xmldom.domdocument;
v_out CLOB;
BEGIN
p := xmlparser.newparser;
xmlparser.setvalidationmode (p, FALSE);
doc :=
'<shipment> <header> <shippingprofile>Oceanside</shippingprofile> <ordernumber>876397</ordernumber> <carrier>FEDEX_CA</carrier> <servicelevel>G</servicelevel> <shipvia>19</shipvia> <shipdate>2008-06-09</shipdate> <shipmentid>3981</shipmentid> <shiprate>58.16</shiprate> <shipaction>SHIP</shipaction> <paymenttype>PREPAID</paymenttype> <address type="shipto"> <attention>TYA</attention> <address1>19889 FRASERHIGHWAY</address1> <city>LANGLEY</city> <state>BC</state> <zipcode>V3A4E1</zipcode> <phone>949-858-5900</phone> <country>CA</country> </address> </header> <packages> <package id="1927"> <db_id>19101</db_id> <weight>59.2</weight> <length>57</length> <width>12</width> <height>16</height> <tracknum>362751270101825</tracknum> <rate>58.16</rate> <contents> <commodityitemcode>Catalytic Converters</commodityitemcode> <description>Automobile Parts</description> <customsvalue>310.95</customsvalue> <producercountry>US</producercountry> <destinationcountry>CA</destinationcountry> </contents><charges> <charge type="base" code="base">53.05</charge> <charge type="additional" code="fuel">2.91</charge> <charge type="special" code="residential">2.2</charge> </charges> <label> XkNDXl5YQV5NQ1leWFpeWEFeTVRUXk1EMTBeUE9OXkxIMCwwXlBSMTIsMTJeTU1UCl5GTzAsMTQ3XkdCODAwLDQsNF5GUwpeRk8wLDQwMV5HQjgwMCw0LDReRlMKXkZPMCw3N DZeR0I4MDAsNCw0XkZTCl5GTzM1LDEyXkFkTiwwLDBeRldOXkZIXkZERnJvbTpeRlMKXkZPMzUsMzFeQWROLDAsMF5GV05eRkheRkRNaWtlIFdyaWdodF5GUwpeRk8zNSw1MV 5BZE4sMCwwXkZXTl5GSF5GRExvZ2ljb3IgSW5jXkZTCl5GTzM1LDcxXkFkTiwwLDBeRldOXkZIXkZENDEgTW91aW50YWluIEF2ZV5GUwpeRk8zNSw5Ml5BZE4sMCwwXkZXTl5 GSF5GRF5GUwpeRk8zNSwxMTJeQWROLDAsMF5GV05eRkheRkRNQUxERU4sIE1BIDAyMTQ4XkZTCl5GTzM1LDEzMl5BZE4sMCwwXkZXTl5GSF5GRDQ4MC04NTctNzkwMF5GUwpe Rk80OTAsMzFeQWROLDAsMF5GV05eRkheRkRTaGlwIERhdGU6IDlKVU4yMDA4XkZTCl5GTzQ5MCw1MV5BZE4sMCwwXkZXTl5GSF5GREFjdHVhbCBXZ3Q6IDY2IExCXkZTCl5GT zQ5MCw3Ml5BZE4sMCwwXkZXTl5GSF5GRFN5c3RlbSM6IDAzNTUwMTgvRlhSUzA3NjReRlMKXkZPNDkwLDkxXkFkTiwwLDBeRldOXkZIXkZEQWNjb3VudDogUyAqKioqKioqKi ogXkZTCl5GTzEwLDE2Ml5BME4sMjAsMTheRldOXkZIXkZEVE9eRlMKXkZPNDMsMTU4XkEwTiwyNSwyN15GV05eRkheRkReRlMKXkZPNjAwLDE1Nl5BZE4sMCwwXkZXTl5GSF5 GRDk0OS04NTgtNTkwMF5GUwpeRk80MywxODZeQTBOLDI1LDI3XkZXTl5GSF5GRFRZQV5GUwpeRk80MywyMTZeQTBOLDI1LDI3XkZXTl5GSF5GRDE5ODg5IEZSQVNFUkhJR0hX QVleRlMKXkZPNDMsMjQ2XkEwTiwyNSwyN15GV05eRkheRkReRlMKXkZPNDMsMjc2XkEwTiwzMCwzMF5GV05eRkheRkRMQU5HTEVZLCBCQyBWM0E0RTFeRlMKXkZPNTMwLDMwN V5BME4sMzUsNDVeRldOXkZIXkZEKENBKV5GUwpeRk83MjUsMjMxXkFkTiwwLDBeRldOXkZIXkZER3JvdW5kXkZTCl5GTzY3MCwyNjheR0IxMDUsMTAsMTBeRlMKXkZPNjcwLD I3OF5HQjEwLDExMiwxMF5GUwpeRk83NjUsMjc4XkdCMTAsMTEyLDEwXkZTCl5GTzY3MCwzOTBeR0IxMDUsMTAsMTBeRlMKXkZPNjUwLDE4OF5BME4sNTAsNTVeRldOXkZIXkZ ERmVkRXheRlMKXkZPNjkwLDI4Nl5BME4sMTMwLDEzMF5GV05eRkheRkRHXkZTCl5GTzQ3NiwzXkdCNCwxNDUsNF5GUwpeRk8xMCwzNDheQTBOLDE1LDE1XkZXTl5GSF5GRFJl ZiA4NzYzOTdeRlMKXkZPNDA2LDM4OF5BME4sMTUsMTVeRldOXkZIXkZERGVwdCAxOTI3XkZTCl5GTzEwLDM2OF5BME4sMTUsMTVeRldOXkZIXkZESW52IF5GUwpeRk8xMCwzO DheQTBOLDE1LDE1XkZXTl5GSF5GRFBPIF5GUwpeRk84MCw3NzFeQlk0LDJeQkNOLDI5MCxOLE4sTixOXkZXTl5GRD47Pjg5NjEyMDI2MzYyNzUxMjcwMTAxODI1XkZTCl5GTz EzNSwxMDc4XkEwTiwyNSwyN15GV05eRkheRkQoOTYxMjAyNikgIDM2Mjc1MTIgIDcwMTAxODI1XkZTCl5GTzc4MywyODheQTBOLDE1LDE1XkZXQl5GSF5GRENMUzA2MTMwNS8 xMC8xNV5GUwpeRk8yNSwxMTAzXkEwTiw1MCw1NV5GV05eRkheRkRJTlQtR05EXkZTCl5GTzI1LDExNTBeQTBOLDM1LDQ1XkZXTl5GSF5GRFByZXBhaWReRlMKXkZPMzAwLDEx MTVeQTBOLDM1LDQ1XkZXTl5GSF5GRF5GUwpeRk8zMDAsMTE0OV5BME4sMzUsNDVeRldOXkZIXkZEXkZTCl5GTzMwLDExOTJeQTBOLDIwLDE4XkZXTl5GSF5GRFNFRDpORFIgM zAuNTheRlMKXkZPMzAsNDI4XkJZMiwyXkI3TiwxMCw1LDEyXkZIXkZXTl5GSF5GRFspPl8xRTAxXzFEMDJWM0E0RTFfMUQxMjQ2MzI2XzFEMzYyNzUxMjcwMTAxODI1XzFERk RFQl8xRDM2Mjc1MTJfMUQxNjFfMUQgXzFEMS8xXzFENjBMQl8xRE5fMUQxOTg4OSBGUkFTRVJISUdIV0FZXzFETEFOR0xFWV8xREJDXzFEVFlBXzFFMDZfMUQxMFpHSTAwNF8 xRDEyWjk0OTg1ODU5MDBfMUQyM1pZXzFEMjJaIF8xQ05fMUQyMFowLjAwXzFDMF8xRDlLODc2Mzk3XzFEOTlaR0lOVDAxXzFDODQwXzFDMzEwXzFDQ0FEXzFDQXV0b21vYmls ZSBQYXJ0c18xQ05EUiAzMC41OF8xQ18xRDI2WmU1Y2NfMUNfMURfMUVfMDReRlMKXkZPMDYxMCwxMjE1XkEwTiwxNSwxNV5GRF5GUwpeU
FExLDAwMDAsMDAwMCxOXlhaCg==</label> </package> </packages></shipment>';
--v_out := DOC;
SYS.xmlparser.parseclob (p, doc);
v_xmldoc := SYS.xmlparser.getdocument (p);
--DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
--v_out := SYS.XMLPARSER.PARSECLOB ( P, DOC );
--SYS.XMLDOM.writetoCLOB(v_xmldoc, v_out);
--INSERT INTO TEST (TEST_COLUMN)
--VALUES(V_OUT);
--printElements(v_xmldoc);
printelementattributes (v_xmldoc);
EXCEPTION
WHEN xmldom.index_size_err
THEN
raise_application_error (-20120, 'Index Size error');
WHEN xmldom.domstring_size_err
THEN
raise_application_error (-20120, 'String Size error');
WHEN xmldom.hierarchy_request_err
THEN
raise_application_error (-20120, 'Hierarchy request error');
WHEN xmldom.wrong_document_err
THEN
raise_application_error (-20120, 'Wrong doc error');
WHEN xmldom.invalid_character_err
THEN
raise_application_error (-20120, 'Invalid Char error');
WHEN xmldom.no_data_allowed_err
THEN
raise_application_error (-20120, 'Nod data allowed error');
WHEN xmldom.no_modification_allowed_err
THEN
raise_application_error (-20120, 'No mod allowed error');
WHEN xmldom.not_found_err
THEN
raise_application_error (-20120, 'Not found error');
WHEN xmldom.not_supported_err
THEN
raise_application_error (-20120, 'Not supported error');
WHEN xmldom.inuse_attribute_err
THEN
raise_application_error (-20120, 'In use attr error');
END;
/
CREATE OR REPLACE PROCEDURE printelementattributes (doc xmldom.domdocument)
IS
nl xmldom.domnodelist;
len1 NUMBER;
len2 NUMBER;
n xmldom.domnode;
tag_name xmldom.domelement;
nnm xmldom.domnamednodemap;
v_vchcolumnname VARCHAR2 (4000);
v_vchcolumnvalue VARCHAR2 (4000);
text_value VARCHAR2 (4000) := NULL;
n_child xmldom.domnode;
v_vchguid VARCHAR (100);
v_rulestr VARCHAR2 (500);
v_returnvalue INTEGER;
c_handle INTEGER;
v_ncounter INTEGER;
v_vchtablename VARCHAR2 (4000);
v_vchcolumnnamestring VARCHAR2 (4000);
v_vchcolumnvaluestring VARCHAR2 (4000);
BEGIN
v_vchguid := SYS_GUID ();
-- get all elements
nl := xmldom.getelementsbytagname (doc, '*');
len1 := xmldom.getlength (nl);
-- loop through elements
FOR j IN 0 .. len1 - 1
LOOP
n := xmldom.item (nl, j);
tag_name := xmldom.makeelement (n);
DBMS_OUTPUT.put_line ('tag_name = ' || xmldom.gettagname (tag_name)
|| ':'
);
v_vchtablename := xmldom.gettagname (tag_name);
-- get all attributes of element
nnm := xmldom.getattributes (n);
n_child := xmldom.getfirstchild (n);
text_value := xmldom.getnodevalue (n_child);
DBMS_OUTPUT.put_line ('val=' || text_value);
DBMS_OUTPUT.put_line ('');
IF (xmldom.isnull (nnm) = FALSE)
THEN
len2 := xmldom.getlength (nnm);
IF v_vchtablename = 'CHARGE'
THEN
v_vchcolumnvaluestring := NULL;
v_vchcolumnnamestring := NULL;
v_vchcolumnname := NULL;
v_vchcolumnvalue := NULL;
v_vchcolumnnamestring := v_vchtablename;
v_vchcolumnvaluestring := text_value;
END IF;
v_ncounter := 0;
-- loop through attributes
FOR i IN 0 .. len2 - 1
LOOP
v_ncounter := v_ncounter + 1;
n := xmldom.item (nnm, i);
v_vchcolumnname := xmldom.getnodename (n);
v_vchcolumnvalue := xmldom.getnodevalue (n);
DBMS_OUTPUT.put ( ' column name and value = '
|| v_vchcolumnname
|| ' = '
|| v_vchcolumnvalue
);
-- Execute the rule!
IF UPPER (v_vchtablename) = 'CHARGE'
THEN
v_vchcolumnnamestring :=
v_vchcolumnnamestring || ',' || v_vchcolumnname;
v_vchcolumnvaluestring :=
v_vchcolumnvaluestring
|| ','
|| NVL (v_vchcolumnvalue, 'NULL');
END IF;
END LOOP;
IF UPPER (v_vchtablename) = 'CHARGE'
THEN
v_rulestr :=
'insert into t_imp_'
|| v_vchtablename
|| '(hjs_parent_id, hjs_node_id, hjs_sequence, hjs_error_number, hjs_error_message, '
|| v_vchcolumnnamestring
|| ') values ('
|| v_vchguid
|| ','
|| v_vchguid
|| ','
|| v_ncounter
|| ','
|| '-1,'
|| v_vchcolumnvaluestring
|| ')';
DBMS_OUTPUT.put_line ('');
-- DBMS_OUTPUT.put_line (v_rulestr);
v_rulestr := '';
END IF;
END IF;
v_vchcolumnnamestring := v_vchcolumnnamestring || ',' || v_vchtablename;
v_vchcolumnvaluestring := v_vchcolumnvaluestring || ',' || text_value;
DBMS_OUTPUT.put_line (v_vchcolumnnamestring);
DBMS_OUTPUT.put_line (v_vchcolumnvaluestring);
END LOOP;
END printelementattributes;
/
|
|
|
|
|
|
| Re: XML Parsing Query Problem [message #338060 is a reply to message #336325] |
Sat, 02 August 2008 21:29  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
First three pointers on posting here:
1) there are silly looking buttons at the top of the text window you are cutting and pasting into. Three in particular help with formatting, one for code, one for quotes, and one for links. use them.
2) there are silly looking buttons at the top of the text window you are cutting and pasting into. Three in particular help with formatting, one for code, one for quotes, and one for links. use them.
3) there are silly looking buttons at the top of the text window you are cutting and pasting into. Three in particular help with formatting, one for code, one for quotes, and one for links. use them.
Now that this is out of the way, let me suggest that to work with xml you should consider these steps:
| Quote: | 1) first understand the format of your xml document. In particular pay attention to the meaning of WELL-FORMED XML vs. MAL-FORMED XML.
2) second understand the tables you want to load
3) construct a query against the tables you are inserting into, that would allow you to extract the data using DBMS_XMLGEN.GETXML. The resulting XML will not be in the same layout as the XML you are getting in your problem. Instead, DBMS_XMLGEN.GETXML will show you the format you need in order to insert using XMLSAVE (or whatever is the equivelant for your version of the ORACLE RDBMS).
4) write or have someone write for you, an XSLT that will convert your input document into the cannonical layout that you got from DBMS_XMLGEN.GETXML.
5) now use XMLSAVE.
|
Here are some of my own posts on ORAFAQ regarding XML. They may help you or not.
Good luck, Kevin
Kevin Meade's blog
Easy XML - a Programming Oriented Approach
Easy XML - Let the Database do the Work
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 17:12:07 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
