Use "OracleXML putXML"  to insert xml doc with nested tags - General XML

I am trying to use OracleXML putXML to insert a xml doc into my oracle 8i database. The problem is that all the content in single tags get inserted without problem, but the content in nested tags don't get inserted at all.
xml doc section is as follows:
<ROWSET>
<ROW>
<LOCATION>philadelphia</LOCATION>
<DESCRIPTION>
<p>beautiful place</p>
<p>historic place</p>
</DESCRIPTION>
</ROW>
</ROWSET>
I want to insert this info into a table with only two fields "location" and "description". Location info is inserted fine, but DESCRIPTION info doesn't get inserted just because it has <p> tags, if the tags are taken out, it gets inserted fine. I don't want to take them out because I want to keep the format. How to solve this?
Anybody has any suggestions?
Thanks,
Wenqi 

Figured it out. Replace <p> with <p> works!
Wenqi 

Can you explain you method to solved it in details!

Related

urgent :post xml for xsql difference with html form post

Hi,
We are developing an application to process xml request
posted by external party. The requests arrive by
http-post operation
We developed and tested with an html-form.
Now we encounter problems in testing with http-post
The xsql page is processed correctly but
insert only fixed text when formatting xml-data as input for
the insert-request, string inserted is empty.
Everything is by the book and html-forms works great.
Is anybody else using xsql to insert xml data in the database on this version? Maybe can you help by looking into a testcase ? Issued a tar but am on terrible deadline
so need help very soon.
Details: HP-UX 11.11 Oracle 9.2.0.4 java1.4 ojdbc 1.4
Tnx in advance,
Jeroen 
Can you explain what you are trying to do... What is the content of the HTTP Post, is it XML ?. What do you want to do with the content of the HTTP Post. 
Mark,
Have an external partner which sends xml-requests.
Have to read those, store them and process them.
Idea is let them
1)post the xml at a url where an xsql page is reading the xml
2) transforming this for an insert into
the database. The column is defined to store the complete xml-message.
3) A trigger takes care of processing the stuff and preparing the output
4) and a query in the xsql-page reads the output from an output table
When developing and testing this with an html-form that posts the xml is works great.
$ cat newclobins.xsql
<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql" >
<xsql:set-session-param name="id">
select msgid.nextval from dual
</xsql:set-session-param>
<xsql:dml>
delete cbs_msg_in where id={#id}
</xsql:dml>
<xsql:insert-request table="cbs_msg_in" column="id,msg" transform="cbsform_1.xsl"/>
<data>
<xsql:query null-indicator="yes">
select msgid,msg from cbs_xml_out where msgid={#id}
</xsql:query>
</data>
</page>
$ cat cbsform_1.xsl
<?xml version="1.0"?>
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
<xsl:for-each select="request">
<ROW>
<MSG><xsl:value-of select="parameters/doc" /></MSG>
<ID><xsl:value-of select="session/id" /></ID>
</ROW>
</xsl:for-each>
</ROWSET>
1) When using a tool like
http://www.programmersheaven.com/articles/adrian/getpost/article.html it fails with
No rows to modify -- the row enclosing tag missing. Specify the correct row enclosing tag.
2) When using the commandline utility like
xsql jeroen5.xsql posted-xml=test3.xml
this works great when inserting plain text but not
when inserting xml-format data into a column
So problem 1 is why external tools like above or my partner that uses .net to do something similar gives this strange error?
Problem nr 2 is should I stick to just the data in xml and forget about storing the xml-formatted data?
Hope you can help because I am getting quite desparate here.
Regards,
Jeroen 
To some extend I'm not sure what value XSQL is adding here. However I've never used it so I'm probably missing something..
Is the XML Content you are recieving have a namespace or XML Schema associated with it...
Can you post an example of the incoming document
Hi
I have tested with a simple testcase like
<CBSMessage >
test message
</CBSMessage>
We expect messages like
<CBSMessage xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.
com/CBSrequest2.xsd">
<Header>
<Version>1</Version>
<Sender>CBS</Sender>
<Receiver>IRIS</Receiver>
<Guid>6BC096BB-0CE6-11D1-BAAE-00C04FC2E20D</Guid>
</Header>
<Request>
<Type>RequestContractList</Type>
<RequestID>26</RequestID>
<Order>6</Order>
<RequestContractList/>
</Request>
</CBSMessage>
So we did both and allways had the same problems 
I might add we have considered other ways of communicating but we think this might be the easiest way.
External supplier has a website and comes through http with xml messages already inside our network. The website is interactive so we thought xsql calls might be most appropriate way. 
Unfortunately I have no experiene of XSQL so you may be better off reposting the question on the XML Technology forum where most XSQL usage is dealt with. XSQL is not technically regarded as an XML DB feature...
http://forums.oracle.com/forums/index.jsp?cat=51 
Jeroen,
Have you tried to run trace against your connection?
Also, does your OS need an escape character so it doesn't inappropriately interpret the line as a command? 
Hi,
Trace is not an option because data doesn't get into the database at all. It goes wrong much earlier. At the moment am at the stage the problem resides in the http request. It arrives with the wrong content-type. Should be text/xml But when my customer provides this some data arrives but doesn't seem to be able to recoginize the data. An empty row is inserted
Jeroen

XMLDocumentFragments

I have 3 sperate queries which return either Strings or XMLDom's (Don't know which is best yet).....
They need to be combined to make one XMLDom.
I cannot use a nested query as the query would need to look like this
"select cursor(...........),
cursor(...........),
cursor(...........)"
No from clause for main query hence 3 seperate queries.
What would be the best way to do this????
Is it possible to convert a XMLDom to a doc fragment and then append to a new XMLDom
OR
Return all data as strings and concatenate them all together in which case how do I get rid of "<?xml version = '1.0'?>"
TIA
Owen Thomas
null 
Think I found part of the solution
See thread January 04, 2000
Topic "getXMLString(Node) Question" Steve Muench posted a piece of code to do exactly what I needed. However the output still needs some formatting.
My output looks like
<Reference_Data>
<ROWSET>
<ROW num="1">
<GROUPID>1</GROUPID>
<DESCRIPTION>1</DESCRIPTION>
</ROW>
<ROW num="2">
<GROUPID>2</GROUPID>
<DESCRIPTION>Group 2</DESCRIPTION>
</ROW>
</ROWSET>
</Reference_Data>
The Rowset Tag I can change using setRowSetTag( String ) and the Row Tag can be changed using setRowTag.
BUT
I don't want the Rowset AND the Row tags - I would prefer not to have the Rowset Tag. Therefore do I need to format the resulting XML document (which is going to be stored as a CLOB) using XSLT before storing it in the database?
This is all being done through Java and PL/SQL through a call from a batch process so will not need to be displayed on the Net.
TIA
Owen
null

Returning XSQL Metadata.

Excuse my post, I have exausted the Oracle forums and it was exausting...!!!
I have my xsql page working ok.
What I basically want now is for the xsql to generate xml to show the datatype as well as the data in the attribute heading for instance.
Let me give you an example:
<row>
<field1 type='varchar2'></field1>
<field2 type='number'></field2>
.
.
<fieldn type='....'></fieldn>
</row>
Many thanks. 
Using XML Metadata Properties in XSQL Files
The XSQL Metadata Properties 
http://www.oracle.com/webapps/online-help/jdeveloper/10.1.2/state/content/vtTopicFile.creating_xsql%7Ccreating_xsql%7Cxsql_rxmlmetadataprops%7Ehtml/navId.4/navSetId._/

missing characters in XML-result

Hi all,
on 23-12-1999 Vadim posted the following message. He did not get a satisfactory answer and as i have the same problem when using dbms_output (both in SQL+ and with TOAD) i post this one again.
The problem is that a character is missing and the text after this missing character starts on a new line. This way i can't use the XML.
Thanks, Bart
----------------------------------------
Hi, I'm trying to run an example from SQLUtility document to generate an XML from
sqlString = 'SELECT * FROM SCOTT.EMP';
Generated XML CLOB looks like the one below, being cut in the middle. Any suggestions are appreciated.
Thanks, Vadim
<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMI
</ENAME>
</ROW>
</ROWSET>
null 
Hi,
The CLOB will have the correct result. It is probably the DBMS_OUTPUT which is cutting it out, since u will be using put_line cutting the XML documents at arbitrary points.
is the lob itself in your case wrong?
Murali

xsql insert-request for XMLType columns

Is it possible to use the insert-request in XSQL to insert posted xml into a column?
I can successfully use insert-request to insert data into other coumn types (VARCHAR2, NUMBER) but i'm having no luck with the xmltypes.
Here's an example of the styled xml i'm posting to a table with 3 columns, the last being of XMLType...
<ROWSET>
<ROW>
<ID>999</ID>
<PROCESSED>1</PROCESSED>
<XMLCOL1>
<xrat><CRNCY_ID>186</CRNCY_ID><RATE>0.2666</RATE><RATE_DATE>31/12/2001</RATE_DATE><RATE_DATA_SRC_ID>3</RATE_DATA_SRC_ID><EMPNO>100930</EMPNO><CHANGED_TIMESTMP>2002-02-25</CHANGED_TIMESTMP></xrat>
</XMLCOL1>
</ROW>
</ROWSET>
I get no errors back & the ID & PROCESSED columns are populated, but the XMLCOL1 column is still empty?
I'd be grateful for any pointers.
cheers 
Is the contents of the <XMLCOL1> element in your document a nested tree of actual DOM elements, or is it an appropriately quoted XML text representation of the document you are trying to insert?
Not sure if the discussion forum software has made quoted things look unquoted, so it was best to ask specifically. 
I noticed that this problem seems similar to the one I'm facing.
Andrew appears to be using Oracle 9i; we're using 8i, so
don't have access to xmltype; the symptoms look similar however.
See:
Re: ''Urgent'' incorrect environment variable plus_dflt program execution erro
I implemented a simple HTML page to allow the posting of xml text to
an xsql page containing insert-request; when you type in a file like:
<ROWSET>
<ROW>
<COL1>simple data</COL1>
<COL2>more simple data</COL2>
<COL3><MARKEDUPDATA><MARKEDUPDATA1>simple data</MARKEDUPDAT1><MARKEDUPDATA2>simple data</MARKEDUPDAT2>
</MARKEDUPDATA></COL3>
</ROW>
<ROWSET>
the content of COL3 is set and contains xml markup.
So would seem the problem is a stylesheet to create the above document
from this one (or its source document).
<ROWSET>
<ROW>
<COL1>simple data</COL1>
<COL2>more simple data</COL2>
<COL3><MARKEDUPDATA><MARKEDUPDATA1>simple data</MARKEDUPDAT1><MARKEDUPDATA2>simple data</MARKEDUPDAT2>
</MARKEDUPDATA></COL3>
</ROW>
<ROWSET>
Peter
Chapter 16 of my Building Oracle XML Applications has an example of an XSLT Java extension function that takes in a tree of nodes and outputs its textual XML serialization which is what you need to do this. The source code is downloadable from the O'Reilly web site. 
You are absolutely right. This solved my problem. Thank you. I hope it does the
same for Andrew's.
Peter 
Yep. this has solved my problem too,
thanks for your help

Categories

Resources