CODEWITHSUNDEEP
xmloracle-database
ERS
ERS

Reputation: 81

Display < and > in Oracle XML Result

I am using XMLElement and XMLAgg functions in Oracle 11g to generate an XML String. This XML String this then sent to another system for parsing. I want to append the following text to the result of my XML.

The problem is, if I append the text as is, the SQL Statement returns the following - 

    <ZAVACOR_RESULTS>
      <TEST>
        <BATCH>
          &lt;BATCH_OBJECTS&gt;&lt;ORDER_NUM&gt; 0     &lt;/ORDER_NUM&gt;&lt;/BATCH_OBJECTS&gt;
        </BATCH>
      </TEST>
    </ZAVACOR_RESULTS>

I want the result to look like this - 

    <ZAVACOR_RESULTS>
      <TEST>
        <BATCH>
          <BATCH_OBJECTS><ORDER_NUM>0</ORDER_NUM></BATCH_OBJECTS>
        </BATCH>
      </TEST>
    </ZAVACOR_RESULTS>

This is my query. Any help would be much appreciated: 

    SELECT
    XMLElement("ZAVACOR_RESULTS",
    XMLAgg( 
        XMLElement("TEST",
            XMLElement("BATCH", (case when t.batch is NULL then '<BATCH_OBJECTS>            <ORDER_NUM> 0 </ORDER_NUM></BATCH_OBJECTS>' else t.batch end)),    
            (SELECT 
                XMLAgg( 
                    XMLElement("BATCH_OBJECTS",
                        XMLElement("ORDER_NUMBER", (case when bo.ORDER_NUMBER is NULL     then 0 else bo.ORDER_NUMBER end))  
                                )
                       )
             FROM BATCH_OBJECTS bo WHERE t.SAMPLE_NUMBER = bo.OBJECT_ID and                                     
             t.SAMPLE_NUMBER = 705000088556  and t.TEST_NUMBER = 4537048 
                          )
                         )
                    )
        ).getClobVal() as XML 
    FROM TEST T WHERE t.SAMPLE_NUMBER = 705000088556 and t.test_number = 4537048;

Data Relationships: 

TEST.TEST_NUMBER ---Number, Primary Key
TEST.BATCH --- Varchar2, Can be NULL
TEST.SAMPLE_NUMBER --- Number

BATCH_OBJECTS.BATCH --- Varchar2, Part of Composite Key
BATCH_OBJECTS.ORDER_NUMBER --- Number, Part of Composite Key

Upvotes: 0

Views: 1353

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You need turn your fixed string into its own XML object. You haven't shown the table structures. If test.batch is a varchar2 (rather than an XMLType) you can convert the result of your case:

SELECT
XMLElement("ZAVACOR_RESULTS",
XMLAgg( 
    XMLElement("TEST",
        XMLElement("BATCH", xmltype(case when t.batch is NULL then '<BATCH_OBJECTS>            <ORDER_NUM> 0 </ORDER_NUM></BATCH_OBJECTS>' else t.batch end)),    
...

If test.batch is an XMLType then just convert the string literal:

SELECT
XMLElement("ZAVACOR_RESULTS",
XMLAgg( 
    XMLElement("TEST",
        XMLElement("BATCH", case when t.batch is NULL then xmltype('<BATCH_OBJECTS>            <ORDER_NUM> 0 </ORDER_NUM></BATCH_OBJECTS>') else t.batch end),    
...

Either way these give:

XML
-------------------------------------------------------
<ZAVACOR_RESULTS><TEST><BATCH><BATCH_OBJECTS>
  <ORDER_NUM> 0 </ORDER_NUM>
</BATCH_OBJECTS>
</BATCH></TEST></ZAVACOR_RESULTS>

SQL Fiddle.

Based on your description of what you're trying to do - which would be much easier to understand with sample data and expected output - I think what you really want is an outer join to the batch objects table, and an NVL of the order number in case there isn't one:

SELECT XMLSerialize(CONTENT
    XMLElement("ZAVACOR_RESULTS",
      XMLElement("TEST",
        XMLElement("BATCH",
          XMLElement("BATCH_OBJECTS",
            XMLAgg(
              XMLElement("ORDER_NUMBER", nvl(bo.ORDER_NUMBER, 0))
            )
          )
        )
      )
    ) INDENT SIZE = 2
  ) as XML
FROM TEST T
LEFT JOIN BATCH_OBJECTS bo ON bo.BATCH = t.BATCH
WHERE t.SAMPLE_NUMBER = 705000088556
AND t.test_number = 4537048;

With XMLSerialise just to display neatly; to pass to another application you can stick with getClobVal:

<ZAVACOR_RESULTS>
  <TEST>
    <BATCH>
      <BATCH_OBJECTS>
        <ORDER_NUMBER>0</ORDER_NUMBER>
      </BATCH_OBJECTS>
    </BATCH>
  </TEST>
</ZAVACOR_RESULTS>

For a test with batch_objects records that gives:

<ZAVACOR_RESULTS>
  <TEST>
    <BATCH>
      <BATCH_OBJECTS>
        <ORDER_NUMBER>12345</ORDER_NUMBER>
        <ORDER_NUMBER>23456</ORDER_NUMBER>
      </BATCH_OBJECTS>
    </BATCH>
  </TEST>
</ZAVACOR_RESULTS>

SQL Fiddle.

If you want to combine multiple tests into one XML document you can have another level of XMLAgg:

SELECT XMLSerialize(CONTENT
    XMLElement("ZAVACOR_RESULTS",
      XMLAgg(
        XMLElement("TEST", XMLAttributes(t.TEST_NUMBER),
          XMLElement("BATCH", XMLAttributes(t.BATCH),
            XMLElement("BATCH_OBJECTS",
              XMLAgg(
                XMLElement("ORDER_NUMBER", nvl(bo.ORDER_NUMBER, 0))
              )
            )
          )
        )
      )
    ) INDENT SIZE = 2
  ) as XML
FROM TEST T
LEFT JOIN BATCH_OBJECTS bo ON bo.BATCH = t.BATCH
GROUP BY t.TEST_NUMBER, t.BATCH;

I've included some attributes to distinguish the source rows:

<ZAVACOR_RESULTS>
  <TEST TEST_NUMBER="4537048">
    <BATCH>
      <BATCH_OBJECTS>
        <ORDER_NUMBER>0</ORDER_NUMBER>
      </BATCH_OBJECTS>
    </BATCH>
  </TEST>
  <TEST TEST_NUMBER="4537049">
    <BATCH BATCH="BATCH_1">
      <BATCH_OBJECTS>
        <ORDER_NUMBER>12345</ORDER_NUMBER>
        <ORDER_NUMBER>23456</ORDER_NUMBER>
      </BATCH_OBJECTS>
    </BATCH>
  </TEST>
</ZAVACOR_RESULTS>

SQl Fiddle.

Upvotes: 1

Related Questions