| Oracle XMLSAVE & XSL [message #295239] |
Mon, 21 January 2008 19:27 |
cakask
Messages: 11
Registered: January 2008
|
Junior Member |
|
|
We are using Oracle 9.2.0.8.0
I am trying to execute the following code to update the view - which actually means executing the INSTEAD OF trigger. . . But while it is executing without "error" (blowing up), it makes the rowcount = 0 and the INSTEAD OF trigger does NOT get executed.
I am going crazy trying to figure out how to TEST this code - and the point was to verify that the XSLT was correct.
One other big question is what is the correct format of
the clause: (in the example here - the XML tags have the '-' dashes and the ORacle Columns have the '_' Underscores)
<BATCH-UNIQUE-ID><xsl:value-of select="GVN-RECORDS/Header/BATCH_UNIQUE_ID"/></BATCH-UNIQUE-ID>
SO I am assuming - because I cannot find ANYTHING after hours of searching - that the format is
<TAG><xsl:value-of select="path/COLUMNNAME"/></TAG>
The body of the code is here:
l_ctx := DBMS_XMLSave.newContext('V_VEND_XML_H');
DBMS_XMLSave.setXSLT(l_ctx, GC_ifmis_vend_h_xsl_to_canonic, null);
DBMS_XMLSave.setDateFormat(l_ctx,'YYYYMMDD');
DBMS_XMLSave.setIgnoreCase(l_ctx, 1); --IGNORE_CASE.
DBMS_XMLSave.clearKeyColumnList(l_ctx);
DBMS_XMLSave.clearUpdateColumnList(l_ctx);
DBMS_XMLSave.setKeyColumn(l_ctx,'BATCH_UNIQUE_ID');
DBMS_XMLSave.setUpdateColumn(l_ctx,'ADD_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'MODIFY_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'DELETE_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'REACTIVATE_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'TRANSACTION_DATE');
DBMS_XMLSave.setUpdateColumn(l_ctx,'ACTION_TIME');
DBMS_XMLSave.setCommitBatch(l_ctx, 0);
V_rowcount := DBMS_XMLSave.updateXML(l_ctx, v_clob);
A sample segment of the XML is shown here...
<?xml version = '1.0'?>
<GVN-RECORDS>
<Header>
<BATCH-UNIQUE-ID>ABCDE</BATCH-UNIQUE-ID>
<ADD-COUNT>0</ADD-COUNT>
<MODIFY-COUNT>2</MODIFY-COUNT>
<DELETE-COUNT>0</DELETE-COUNT>
<REACTIVATE-COUNT>9</REACTIVATE-COUNT>
<TRANSACTION-DATE>20071227</TRANSACTION-DATE>
<ACTION-TIME>100805</ACTION-TIME>
<Detail>
<TRAN-TYPE>M</TRAN-TYPE>
</Detail>
</Header>
</GVN-RECORDS>
And my XSL is shown here:
GC_ifmis_vend_h_xsl_to_canonic constant clob :=
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<ROWSET>
<ROW>
<BATCH-UNIQUE-ID><xsl:value-of select="GVN-RECORDS/Header/BATCH_UNIQUE_ID"/></BATCH-UNIQUE-ID>
<ADD-COUNT><xsl:value-of select="GVN-RECORDS/Header/ADD_COUNT"/></ADD-COUNT>
<MODIFY-COUNT><xsl:value-of select="GVN-RECORDS/Header/MODIFY_COUNT"/></MODIFY-COUNT>
<DELETE-COUNT><xsl:value-of select="GVN-RECORDS/Header/DELETE_COUNT"/></DELETE-COUNT>
<REACTIVATE-COUNT><xsl:value-of select="GVN-RECORDS/Header/REACTIVATE_COUNT"/></REACTIVATE-COUNT>
<TRANSACTION-DATE><xsl:value-of select="GVN-RECORDS/Header/TRANSACTION_DATE"/></TRANSACTION-DATE>
<ACTION-TIME><xsl:value-of select="GVN-RECORDS/Header/ACTION_TIME"/></ACTION-TIME>
</ROW>
</ROWSET>
</xsl:template>
</xsl:stylesheet>';
I hope I have supplied enough info - but I would be happy to add dtls if needed
=====================
New info - on my way to my stroke  , I tried one more thing. In my test file, I changed the XML tags to make all of the dashes(-) into underscores (_) and then changed the references in the PL/SQL code (in the XSL) to also only have underscores. FINALLY that worked. . BUT I had been instructed that I HAD to use the tags as they were defined - meaning with the dashes(-).
So I guess I still need to know - as I have been unable to find any examples after HOURS of searching - of how to code the XSL / Oracle column mapping in the example shown above.
Thanks so much for any ideas.
[Updated on: Mon, 21 January 2008 21:05] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
