Solved

Oracle XML Help

Posted on 2013-11-13
8
432 Views
Last Modified: 2013-11-13
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
Comment
Question by:nssudha
  • 4
  • 4
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39644993
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
 

Author Comment

by:nssudha
ID: 39645049
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
 

Author Comment

by:nssudha
ID: 39645073
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39645078
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
 

Author Comment

by:nssudha
ID: 39645106
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39645120
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39645130
>>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
 

Author Comment

by:nssudha
ID: 39645274
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now