Solved

Oracle XML Help

Posted on 2013-11-13
8
429 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
 
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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

920 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

14 Experts available now in Live!

Get 1:1 Help Now