• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Oracle XML Help

Hi,

I am generating an XML file through SQL query. I have a descriptive column output with open and closed brackets inside the output. For example: Congestive Heart Failure (CHF). When I generate XML, I am getting an error LPX-00210: expected '=' insetad of '>' Error at line 1. Could you please help me resolve this issue?

Thanks in advance!
Sudhakar.
0
nssudha
Asked:
nssudha
  • 4
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
You are likely using string concatenation to generate the XML?

I suggest you use the native SQL calls like XMLELEMENT, XMLFOREST, etc... to do it.  This will properly encode the data.

If you can provide some sample data and expected results, we can create a test case for you.
0
 
nssudhaAuthor Commented:
I am using native SQL calls only. XMLSERIALIZE, XMLELEMENT, XMLFOREST, XMLAGG etc. In fact, I was helped by one of the experts here to spool the XML result to a file in a custom XML output.

Thanks,
Sudhakar.
0
 
nssudhaAuthor Commented:
Hi slightwv,

You only helped my answer my question to generate xml. Here is the answer you provided me. I am using the same one to generate another XML. In that the descriptive field with what I explained in my question was giving issues to me.

Thanks,
Sudhakar.

drop table tab1 purge;
create table tab1(col1 clob);

insert into tab1 (
select xmlserialize(document
      xmlelement("FootNoteDetails",
            xmlelement("HeaderInformation",
                  xmlforest(
                        'ORDR-Footnote' as "DataSource",
                        sysdate as "DateCreated",
                        sysdate as "DateLastUpdated",
                        mycount as "OrdersCount"
                  )
            ),
            xmlagg(
                  xmlelement("FootNote",
                        xmlforest(
                              trim(orderid) as "OrderID",
                              orderdesc as "OrderDesc"
                        )
                  )
            order by orderid)
      )
version '1.0' indent)
from (
select orderid, orderdesc, count(*) over() mycount from myorders
)
group by mycount
)
/
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Are you sure it is a data problem?

Can you post sample data and the query that generates the error so we can try to reproduce it?

We need something to go on other than just the error message.
0
 
nssudhaAuthor Commented:
When I initially got the error for the entire file, I removed all the columns except for one and kept on trying to generate the file. When I added the descriptive column to the output, I got the error. When I removed it and kept all others, I got the output without any errors. That's how I came to the conclusion. Can you use the same example I provided to you in my above reply? You may just have to add a comments field and insert a value with open and closed parenthesis as I explained in my question above.

Thanks,
Sudhakar.
0
 
slightwv (䄆 Netminder) Commented:
Looks like we cross posted.

I was able to run what you posted using 11.2.0.2 and 12.1.0.1.

Does it fail for you?

If so, what is your Oracle version (all 4 numbers please).
0
 
slightwv (䄆 Netminder) Commented:
>>You may just have to add a comments field and insert a value with open and closed parenthesis as I explained in my question above.

Please add to the test case with data that generates the error.  I cannot make it up for you.  You know what is generating the error.

You may need to experiment with your actual data to narrow down the exact row/column causing it.

Or, you are introducing a syntax error when editing the script.

Either way, I need something that I can run to reproduce the error.
0
 
nssudhaAuthor Commented:
Please ignore my question. It was my mistake while typing the identifier for the descriptive field.

I typed Comment as "as Comnt". "as" inside the identifier caused the issue.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now