Home » Developer & Programmer » JDeveloper, Java & XML » Please help with XML parsing (Oracle 11.2.0.4)
Please help with XML parsing [message #638269] Mon, 08 June 2015 13:43 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Hi,

Can you please fix this xml sql, so that it displays the data of req_no column along with id, and want to put a filter on that req_no column.

WITH wnp_incoming_trx
     AS (SELECT 1 id,
                '​<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                    <PORT_REQUEST_ACK>
                      <HEADER>
                        <ORIGINATOR>SMG</ORIGINATOR>
                        <DESTINATION>ZIG</DESTINATION>
                        <MESSAGE_ID>ZIG2921251</MESSAGE_ID>
                        <MSGTYPE>PQA</MSGTYPE>
                        <TIMESTAMP>08222014225313</TIMESTAMP>
                      </HEADER>
                      <REQ_NO>6214014234970033</REQ_NO>
                      <VER_ID_REQ>00</VER_ID_REQ>
                      <NLSP>X508</NLSP>
                      <NNSP>6214</NNSP>
                      <ONSP>6017</ONSP>
                      <NRSELLNM>altX508 ATTM_ALT/3</NRSELLNM>
                      <D_TSENT>082220142253</D_TSENT>
                    </PORT_REQUEST_ACK>​'
                   xml_message
           FROM DUAL)
SELECT t.ID, i.REQ_NO
  FROM wnp_incoming_trx t,
       XMLTABLE ('/PORT_REQUEST_ACK'
                 PASSING t.xml_message
                 COLUMNS REQ_NO VARCHAR2 (200) PATH 'REQ_NO') i



Thanks,
Manu

Report message to a moderator

Re: Please help with XML parsing [message #638271 is a reply to message #638269] Mon, 08 June 2015 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What about a WHERE clause?

Report message to a moderator

Re: Please help with XML parsing [message #638272 is a reply to message #638271] Mon, 08 June 2015 13:55 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
I was just breaking it down into sub problem.

The sql is not even executing. I validated my xml data here.

http://www.xmlvalidation.com/index.php?id=1&L=0&target=/xmlvalidation/start.jsp;jsessionid=BBFD8738C79E8CC8B340B8438D457FD3

WITH wnp_incoming_trx
     AS (SELECT 1 id,
                '​<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                    <PORT_REQUEST_ACK>
                      <HEADER>
                        <ORIGINATOR>SMG</ORIGINATOR>
                        <DESTINATION>ZIG</DESTINATION>
                        <MESSAGE_ID>ZIG2921251</MESSAGE_ID>
                        <MSGTYPE>PQA</MSGTYPE>
                        <TIMESTAMP>08222014225313</TIMESTAMP>
                      </HEADER>
                      <REQ_NO>6214014234970033</REQ_NO>
                      <VER_ID_REQ>00</VER_ID_REQ>
                      <NLSP>X508</NLSP>
                      <NNSP>6214</NNSP>
                      <ONSP>6017</ONSP>
                      <NRSELLNM>altX508 ATTM_ALT/3</NRSELLNM>
                      <D_TSENT>082220142253</D_TSENT>
                    </PORT_REQUEST_ACK>'
                   xml_message
           FROM DUAL)
SELECT t.ID, i.REQ_NO
  FROM wnp_incoming_trx t,
       XMLTABLE ('/PORT_REQUEST_ACK'
                 PASSING xmltype (t.xml_message)
                 COLUMNS REQ_NO VARCHAR2 (200) PATH 'REQ_NO') i


XML seems fine, but it's not getting executed.
Once this will execute, I will try to include where condition.

Thanks,
Manu

Report message to a moderator

Re: Please help with XML parsing [message #638273 is a reply to message #638272] Mon, 08 June 2015 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

So first make it execute.

SQL> WITH wnp_incoming_trx
  2       AS (SELECT 1 id,
  3                  '?<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  4                      <PORT_REQUEST_ACK>
  5                        <HEADER>
  6                          <ORIGINATOR>SMG</ORIGINATOR>
  7                          <DESTINATION>ZIG</DESTINATION>
  8                          <MESSAGE_ID>ZIG2921251</MESSAGE_ID>
  9                          <MSGTYPE>PQA</MSGTYPE>
 10                          <TIMESTAMP>08222014225313</TIMESTAMP>
 11                        </HEADER>
 12                        <REQ_NO>6214014234970033</REQ_NO>
 13                        <VER_ID_REQ>00</VER_ID_REQ>
 14                        <NLSP>X508</NLSP>
 15                        <NNSP>6214</NNSP>
 16                        <ONSP>6017</ONSP>
 17                        <NRSELLNM>altX508 ATTM_ALT/3</NRSELLNM>
 18                        <D_TSENT>082220142253</D_TSENT>
 19                      </PORT_REQUEST_ACK>'
 20                     xml_message
 21             FROM DUAL)
 22  SELECT t.ID, i.REQ_NO
 23    FROM wnp_incoming_trx t,
 24         XMLTABLE ('/PORT_REQUEST_ACK'
 25                   PASSING xmltype (t.xml_message)
 26                   COLUMNS REQ_NO VARCHAR2 (200) PATH 'REQ_NO') i
 27  /
                 PASSING xmltype (t.xml_message)
                         *
ERROR at line 25:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '?'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1


The only valid XML is the one XMLTYPE validates whatever you want.
Note I just copied and pasted your query so it seems you have something wrong in your string (note the first ?).

Also you should know that you should post as I posted.

[Updated on: Mon, 08 June 2015 14:11]

Report message to a moderator

Re: Please help with XML parsing [message #638287 is a reply to message #638273] Tue, 09 June 2015 00:54 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
Try:
WITH wnp_incoming_trx AS 
 (SELECT 1 id, XMLTYPE
     ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
       <PORT_REQUEST_ACK>
         <HEADER>
           <ORIGINATOR>SMG</ORIGINATOR>
           <DESTINATION>ZIG</DESTINATION>
           <MESSAGE_ID>ZIG2921251</MESSAGE_ID>
           <MSGTYPE>PQA</MSGTYPE>
           <TIMESTAMP>08222014225313</TIMESTAMP>
         </HEADER>
         <REQ_NO>6214014234970033</REQ_NO>
         <VER_ID_REQ>00</VER_ID_REQ>
         <NLSP>X508</NLSP>
         <NNSP>6214</NNSP>
         <ONSP>6017</ONSP>
         <NRSELLNM>altX508 ATTM_ALT/3</NRSELLNM>
         <D_TSENT>082220142253</D_TSENT>
       </PORT_REQUEST_ACK>') xml_message
    FROM DUAL)
SELECT t.id, i.req_no
  FROM wnp_incoming_trx t,
       XMLTABLE ('/PORT_REQUEST_ACK'
         PASSING  t.xml_message
         COLUMNS REQ_NO VARCHAR2 (200) PATH 'REQ_NO') i

i   REQ_NO
-----------------
1  6214014234970033


But I just see, also works:
WITH wnp_incoming_trx AS 
 (SELECT 1 id, 
     '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
       <PORT_REQUEST_ACK>
         <HEADER>
           <ORIGINATOR>SMG</ORIGINATOR>
           <DESTINATION>ZIG</DESTINATION>
           <MESSAGE_ID>ZIG2921251</MESSAGE_ID>
           <MSGTYPE>PQA</MSGTYPE>
           <TIMESTAMP>08222014225313</TIMESTAMP>
         </HEADER>
         <REQ_NO>6214014234970033</REQ_NO>
         <VER_ID_REQ>00</VER_ID_REQ>
         <NLSP>X508</NLSP>
         <NNSP>6214</NNSP>
         <ONSP>6017</ONSP>
         <NRSELLNM>altX508 ATTM_ALT/3</NRSELLNM>
         <D_TSENT>082220142253</D_TSENT>
       </PORT_REQUEST_ACK>' xml_message
    FROM DUAL)
SELECT t.id, i.req_no
  FROM wnp_incoming_trx t,
       XMLTABLE ('/PORT_REQUEST_ACK'
         PASSING  XMLTYPE(t.xml_message)
         COLUMNS REQ_NO VARCHAR2 (200) PATH 'REQ_NO') i  

i   REQ_NO
-----------------
1  6214014234970033


Update: May be in your code the "<" is written escaped as "&lt;" and the ">" as "&gt;"?

[Updated on: Tue, 09 June 2015 01:10]

Report message to a moderator

Previous Topic: SQL & xml
Next Topic: Help adding more fields in XML?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

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