|
|
 next
|
  Subject: Generating XML file from tables in local machine
Author: Vijay karthik B
Date: 09 May 2005 03:47 AM
|
Dear all
I've a problem i want to create a XML file from tables in a local machine thru local machine
my code is
table script
CREATE TABLE PVUSR28FEB05
(
PROV_C NUMBER(9),
PROV_C_EXT NUMBER(3),
PROV_PASS VARCHAR2(15 BYTE),
SESSIONID VARCHAR2(100 BYTE)
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PK_PVUSR ON PVUSR28FEB05
(PROV_C, PROV_C_EXT)
LOGGING
NOPARALLEL;
ALTER TABLE PVUSR28FEB05 ADD (
CONSTRAINT PK_PVUSR PRIMARY KEY (PROV_C, PROV_C_EXT));
GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON PVUSR28FEB05 TO PUBLIC;
PROV_C ,
PROV_C_EXT,
PROV_PASS,
SESSIONID
Procedure---
CREATE OR REPLACE PROCEDURE Write_Xml(
p_prov_c IN PVUSR28FEB05.PROV_C%TYPE,
p_prov_c_ext IN PVUSR28FEB05.PROV_C_EXT%TYPE) AS
doc xmldom.DOMDocument;
main_node xmldom.DOMNode;
root_node xmldom.DOMNode;
user_node xmldom.DOMNode;
item_node xmldom.DOMNode;
root_elmt xmldom.DOMElement;
item_elmt xmldom.DOMElement;
item_text xmldom.DOMText;
CURSOR get_rec(p_prov_c PVUSR28FEB05.PROV_C%TYPE,p_prov_c_ext PVUSR28FEB05.PROV_C_EXT%TYPE) IS
SELECT PROV_C, PROV_C_EXT, PROV_PASS, SESSIONID, ROWNUM
FROM PVUSR28FEB05
WHERE
PROV_C = p_prov_c AND
PROV_C_EXT = p_prov_c_ext;
BEGIN
doc := xmldom.newDOMDocument;
main_node := xmldom.makeNode(doc);
root_elmt := xmldom.createElement(doc, 'PARAMETERS');
root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
FOR idx IN get_rec(p_prov_c,p_prov_c_ext) LOOP --Start Loop
item_elmt := xmldom.createElement(doc,'DETAILS');
xmldom.setAttribute(item_elmt, 'num', idx.ROWNUM);
user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
-- create user element: PROV_C
item_elmt := xmldom.createElement(doc, 'PROV_C');
item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
item_text := xmldom.createTextNode(doc, idx.PROV_C);
item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
-- create user element: PROV_C_EXT
item_elmt := xmldom.createElement(doc, 'PROV_C_EXT');
item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
item_text := xmldom.createTextNode(doc, idx.PROV_C_EXT);
item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
-- create user element: PROV_PASS
item_elmt := xmldom.createElement(doc, 'PROV_PASS');
item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
item_text := xmldom.createTextNode(doc, idx.PROV_PASS);
item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
-- create user element: SESSIONID
item_elmt := xmldom.createElement(doc, 'SESSIONID');
item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
item_text := xmldom.createTextNode(doc, idx.SESSIONID);
item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
END LOOP; --End of Loop
-- xmldom.writeToFile(doc, '/tmp/xml/docSample.xml'); --Path in which file is created
xmldom.writeToFile(doc, 'C:\docSample.xml');
xmldom.freeDocument(doc); --Freeing the resources
-- Exception handling
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');
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,255));
END Write_Xml;
/
Although the line
xmldom.writeToFile(doc, 'C:\docSample.xml');
is generating a file but in server which i don't want any suggestions or answer pls mail me at bvijaykarthik@yahoo.com
Regards
vijay
|
|
next
|
  Subject: Generating XML file from tables in local machine
Author: Ivan Pedruzzi
Date: 11 May 2005 03:43 PM
|
Try SQL/XML, it is easier
SELECT
XMLELEMENT(name "DETAILS",
XMLATTRIBUTES(P.ROWNUM AS "num"),
XMLELEMENT(name "PROV_C", P.PROV_C),
XMLELEMENT(name "PROV_C_EXT", P.PROV_C_EXT ),
XMLELEMENT(name "PROV_PASS", P.PROV_PASS),
XMLELEMENT(name "SESSIONID", P.SESSIONID)
)
FROM PVUSR28FEB05 P
The above query result looks like this
<DETAILS num='...'>
<PROV_C>..</PROV_C>
<PROV_C_EXT>..</PROV_C_EXT>
<PROV_PASS>...</PROV_PASS>
<SESSIONID>...</SESSIONID>
</DETAILS>
...
Hope this helps
Ivan Pedruzzi
Stylus Studio Team
|
|
|
|
|
top