Home » Developer & Programmer » JDeveloper, Java & XML » XML format for nested xmlelements (Oracle 11g)
XML format for nested xmlelements [message #524978] Wed, 28 September 2011 03:22
vanessavmedschemecoza
Messages: 1
Registered: September 2011
Junior Member
Hi!

I'm new to oracle XML. Need some help on how to get rid of additional tags added when I use nested xmlelements in SQL.

Here is my SQL statement and output:
SQL:
select dbms_xmlgen.getXMLtype(dbms_xmlgen.newcontext('SELECT xmlelement("Cell", xmlattributes(''s64'' AS "ss:StyleID"), xmlElement("Data", xmlattributes(''String'' AS "ss:Type"),atra.acb_tran_status)),
xmlelement("Cell", xmlattributes(''s64'' AS "ss:StyleID"), xmlElement("Data", xmlattributes(''String'' AS "ss:Type"),atra.supplier_type))
FROM mds_acb_transactions atra, mds_acb_runs arun
WHERE arun.acb_run_seq = atra.arun_acb_run_seq
AND arun.acb_run_seq = 11
AND ROWNUM < 3')) bb FROM dual;

output:
<ROWSET>
<ROW>
< XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x00 28__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.ACB_TRAN_STATUS_x0029__ x0029_ >
<Cell ss:StyleID="s64">
<Data ss:Type="String">COMPLETED</Data>
</Cell>
< /XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x0 028__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.ACB_TRAN_STATUS_x0029_ _x0029_ >
< XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x00 28__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.SUPPLIER_TYPE_x0029__x0 029_ >
<Cell ss:StyleID="s64">
<Data ss:Type="String">MEMBER</Data>
</Cell>
< /XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x0 028__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.SUPPLIER_TYPE_x0029__x 0029_ >
</ROW>
<ROW>
< XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x00 28__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.ACB_TRAN_STATUS_x0029__ x0029_ >
<Cell ss:StyleID="s64">
<Data ss:Type="String">COMPLETED</Data>
</Cell>
< /XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x0 028__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.ACB_TRAN_STATUS_x0029_ _x0029_ >
< XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x00 28__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.SUPPLIER_TYPE_x0029__x0 029_ >
<Cell ss:StyleID="s64">
<Data ss:Type="String">VENDOR</Data>
</Cell>
< /XMLELEMENT_x0028__x0022_CELL_x0022__x002C_XMLATTRIBUTES_x0028__x0027_S64_x0027_AS_x0022_SS:STYLEID_x0022__x0029__x002C_XMLELEMENT_x0 028__x0022_DATA_x0022__x002C_XMLATTRIBUTES_x0028__x0027_STRING_x0027_AS_x0022_SS:TYPE_x0022__x0029__x002C_ATRA.SUPPLIER_TYPE_x0029__x 0029_ >
</ROW>
</ROWSET>


desired output:
<ROWSET>
<ROW>
<Cell ss:StyleID="s64">
<Data ss:Type="String">COMPLETED</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">MEMBER</Data>
</Cell>
</ROW>
<ROW>
<Cell ss:StyleID="s64">
<Data ss:Type="String">COMPLETED</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">VENDOR</Data>
</Cell>
</ROW>
</ROWSET>


Your assistance would be much appreciated

Report message to a moderator

Previous Topic: add new multiple new rows
Next Topic: Re: Bigger type length than Maximum
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 00:58:31 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.