OraFAQ Forum: JDeveloper, Java & XML » Looping and querying data from XML clob

Home » Developer & Programmer » JDeveloper, Java & XML » Looping and querying data from XML clob

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Looping and querying data from XML clob [message #598375]

Mon, 14 October 2013 07:27

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Our company database is Oracle based and we use SQL Developer to pull out needed data.

Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.

select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('20458749610')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;


<ASSETTAGDATA>
  <LIST NAME="AssetTag">
    <VALUE SID="1">186037</VALUE>
    <VALUE SID="2">186038</VALUE>
  </LIST>

This query is only able to pull the first value in the list.

I have two things that I am hoping I can get some help with.

Thank you in advance for your help. I hope I have provided enough information. If have I have not, I will gladly respond back with anything else that is needed.

-Matt

Report message to a moderator

Re: Looping and querying data from XML clob [message #598376 is a reply to message #598375]

Mon, 14 October 2013 08:12

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Does "entries" and "items" have the same meaning in your post? If no, explain what are they.
2/ You have to know to define where the wanted string ends. Can you define it?

Report message to a moderator

Re: Looping and querying data from XML clob [message #598377 is a reply to message #598375]

Mon, 14 October 2013 08:18

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, XML you poste isn't a well-formed XML. It lacks </ASSETTAGDATA> tag. Should be:

<ASSETTAGDATA>
  <LIST NAME="AssetTag">
    <VALUE SID="1">186037</VALUE>
    <VALUE SID="2">186038</VALUE>
  </LIST>
</ASSETTAGDATA>

Then you could use XMLTABLE. For example:

with t as (
           select xmltype('<ASSETTAGDATA>
  <LIST NAME="AssetTag">
    <VALUE SID="1">186037</VALUE>
    <VALUE SID="2">186038</VALUE>
  </LIST>
  <LIST NAME="AssetTagX">
    <VALUE SID="10">1</VALUE>
    <VALUE SID="20">2</VALUE>
    <VALUE SID="30">3</VALUE>
  </LIST>
</ASSETTAGDATA>') xmldoc from dual
          )
select  AssetTagName,
        ValueSid,
        Value
  from  t,
        xmltable(
                 '/ASSETTAGDATA/*'
                 passing xmldoc
                 columns
                   AssetTagName varchar2(10) path '/LIST/@NAME',
                   ValueList xmltype path '/LIST/VALUE'
                ),
        xmltable(
                 '/*'
                 passing ValueList
                 columns
                   ValueSid number path '@SID',
                   Value number path '.'
                )
/

ASSETTAGNA   VALUESID      VALUE
---------- ---------- ----------
AssetTag            1     186037
AssetTag            2     186038
AssetTagX          10          1
AssetTagX          20          2
AssetTagX          30          3

SCOTT@orcl >

SY.

[Updated on: Mon, 14 October 2013 08:22]

Report message to a moderator

Re: Looping and querying data from XML clob [message #598378 is a reply to message #598376]

Mon, 14 October 2013 08:22

baggettms01
Messages: 15
Registered: October 2013

Junior Member

1)
In truth, the initial comment about "entries" can be ignored. It was just a little extra information. "Items" refers to the list in the XML that I am trying to pull from.

2)
Yes I can, the wanted string will always be have a > at the beginning and a < at the end.

Thanks. I appreciate any help you can provide.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598394 is a reply to message #598377]

Mon, 14 October 2013 09:21

baggettms01
Messages: 15
Registered: October 2013

Junior Member

The XML file is already in the database(field name: wtr_req_xml). My current query is only able to pull the first value in the AssetTag list. I need the alphanumeric value(s) that contained between the > and the < and I need them concatenated into one column in the query results.

From the provided XML example, I would like the query results to be similar to the below. If the list of values is longer, then the comma-separated list in the Asset_Tag column would have all of those values.

Wtr_Service_Tag    Wtr_Tran_Origin    Wtr_Send_Date    Wtr_Receive Date    Asset_Tag

123ABCD            D                  12-Oct-13        12-Oct-13           186037,186038

Report message to a moderator

Re: Looping and querying data from XML clob [message #598398 is a reply to message #598394]

Mon, 14 October 2013 09:38

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you provide a test case you may be able to correctly follow what you have and want.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598413 is a reply to message #598398]

Mon, 14 October 2013 10:51

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Apologies. My best attempt at a Test Case is below. Please forgive me if there are a few mistakes, I am still a relative newbie to SQL.

CREATE TABLE ws_transactions
(
Wtr_Service_Tag NUMBER(11),
Wtr_Tran_Origin CHAR(1),
Wtr_Send_Date DATE,
Wtr_Receive_Date DATE,
Wtr_Teq_XML CLOB);

INSERT ALL
  INTO wtr_transactions {Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('123ABCD','D',12-Oct-13, 12-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>'
  INTO wtr_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('234ABCD','D',13-Oct-13, 14-Oct-13,<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')

select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('20458749610')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;

Below is my expected output.

Wtr_Service_Tag    Wtr_Tran_Origin    Wtr_Send_Date    Wtr_Receive Date    Asset_Tag
-------------------------------------------------------------------------------------------------------------
123ABCD            D                  12-Oct-13        12-Oct-13           186037, 186038
234ABCD            D                  13-Oct-13        14-Oct-13           12XYZ34567, 12WXY34567, 12VWX34567

Thanks again for any help you may provide. I appreciate your kind guidance towards getting all of the necessary and relevant information to you.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598417 is a reply to message #598413]

Mon, 14 October 2013 10:58

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> INSERT ALL
2 INTO wtr_transactions {Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('123ABCD','D',12-Oct-13, 12-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>'
3 INTO wtr_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('234ABCD','D',13-Oct-13, 14-Oct-13,<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
4 /
ERROR:
ORA-01756: quoted string not properly terminated

The test case seems good but you have to fix the statement.

[Updated on: Mon, 14 October 2013 10:59]

Report message to a moderator

Re: Looping and querying data from XML clob [message #598421 is a reply to message #598417]

Mon, 14 October 2013 11:05

baggettms01
Messages: 15
Registered: October 2013

Junior Member

I think I have corrected the necessary statements. Again, I am fairly new to this. Thanks for your patience. Any guidance you could provide on how to better structure my test case to work properly would be greatly appreciated. I am working with an already established database and was not involved at all in the design/development of it.

CREATE TABLE ws_transactions
  (Wtr_Service_Tag NUMBER(11),
   Wtr_Tran_Origin CHAR(1),
   Wtr_Send_Date DATE,
   Wtr_Receive_Date DATE,
   Wtr_Teq_XML CLOB
  );

INSERT ALL
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('123ABCD','D',12-Oct-13, 12-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('234ABCD','D',13-Oct-13, 14-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('20458749610')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;

Report message to a moderator

Re: Looping and querying data from XML clob [message #598425 is a reply to message #598421]

Mon, 14 October 2013 11:15

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Gahhh....

Hopefully, the third time is the charm. Just found more corrections to be made.

CREATE TABLE ws_transactions
  (Wtr_Service_Tag NUMBER(11),
   Wtr_Tran_Origin CHAR(1),
   Wtr_Send_Date DATE,
   Wtr_Receive_Date DATE,
   Wtr_Teq_XML CLOB
  );

INSERT ALL
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('12345678901','D',12-Oct-13, 12-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',13-Oct-13, 14-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('12345678901')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;

Output samples (obviously differing from line to line based on the single-quoted value in the where statement)

Wtr_Service_Tag    Wtr_Tran_Origin    Wtr_Send_Date    Wtr_Receive Date    Asset_Tag
-------------------------------------------------------------------------------------------------------------
12345678901        D                  12-Oct-13        12-Oct-13           186037, 186038
23456789012        D                  13-Oct-13        14-Oct-13           12XYZ34567, 12WXY34567, 12VWX34567

Report message to a moderator

Re: Looping and querying data from XML clob [message #598430 is a reply to message #598425]

Mon, 14 October 2013 11:42

baggettms01
Messages: 15
Registered: October 2013

Junior Member

I'll get this right eventually, I promise.

Correction to the create statement change "wtr_teq_xml" to "wtr_req_xml"

Report message to a moderator

Re: Looping and querying data from XML clob [message #598432 is a reply to message #598430]

Mon, 14 October 2013 11:59

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> CREATE TABLE ws_transactions
2 (Wtr_Service_Tag NUMBER(11),
3 Wtr_Tran_Origin CHAR(1),
4 Wtr_Send_Date DATE,
5 Wtr_Receive_Date DATE,
6 wtr_req_xml CLOB);

Table created.

SQL> INSERT ALL
2 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('12345678901','D',12-Oct-13, 12-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
3 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',13-Oct-13, 14-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
4 select * from
5 (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
6 to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
7 from ws_transactions
8 Where Wtr_Service_Tag In ('12345678901')
9 And Wtr_Req_Xml Like ('%CSM%')
10 Order By Wtr_Receive_Date Desc)
11 where rownum = 1;
INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',13-Oct-13, 14-Oct-13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
*
ERROR at line 3:
ORA-00904: "OCT": invalid identifier

Report message to a moderator

Re: Looping and querying data from XML clob [message #598433 is a reply to message #598432]

Mon, 14 October 2013 12:25

baggettms01
Messages: 15
Registered: October 2013

Junior Member

CREATE TABLE ws_transactions
  (Wtr_Service_Tag NUMBER(11),
   Wtr_Tran_Origin CHAR(1),
   Wtr_Send_Date DATE,
   Wtr_Receive_Date DATE,
   Wtr_Req_XML CLOB
  );

INSERT ALL
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('12345678901','D',12/10/13, 12/10/13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',13/10/13, 14/10/13,'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('12345678901')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;

Report message to a moderator

Re: Looping and querying data from XML clob [message #598434 is a reply to message #598433]

Mon, 14 October 2013 12:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

STRINGS need to be enclosed in single quote marks
NUMBERS do not require single quote marks; like below:

SQL> select 12/10/13 from dual;

  12/10/13
----------
.092307692

DATE datatype need to be converted from STRING into DATE by using TO_DATE() function.
http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions213.htm#SQLRF06132

Report message to a moderator

Re: Looping and querying data from XML clob [message #598435 is a reply to message #598434]

Mon, 14 October 2013 12:37

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Thanks!

CREATE TABLE ws_transactions
  (Wtr_Service_Tag NUMBER(11),
   Wtr_Tran_Origin CHAR(1),
   Wtr_Send_Date DATE,
   Wtr_Receive_Date DATE,
   Wtr_Req_XML CLOB
  );

INSERT ALL
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES (12345678901,'D',TO_DATE('10/12/13', 'mm/dd/yy'), TO_DATE('12/10/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
  INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',TO_DATE('10/13/13', 'mm/dd/yy'), TO_DATE('10/14/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
select * from 
  (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('12345678901')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc)
where rownum = 1;

Report message to a moderator

Re: Looping and querying data from XML clob [message #598436 is a reply to message #598435]

Mon, 14 October 2013 14:53

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> CREATE TABLE ws_transactions
2 (Wtr_Service_Tag NUMBER(11),
3 Wtr_Tran_Origin CHAR(1),
4 Wtr_Send_Date DATE,
5 Wtr_Receive_Date DATE,
6 Wtr_Req_XML CLOB
7 );

Table created.

SQL>
SQL> INSERT ALL
2 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES (12345678901,'D',TO_DATE('10/12/13', 'mm/dd/yy'), TO_DATE('12/10/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
3 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',TO_DATE('10/13/13', 'mm/dd/yy'), TO_DATE('10/14/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
4 select * from
5 (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
6 to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
7 from ws_transactions
8 Where Wtr_Service_Tag In ('12345678901')
9 And Wtr_Req_Xml Like ('%CSM%')
10 Order By Wtr_Receive_Date Desc)
11 where rownum = 1;

0 rows created.

SQL>
SQL>
SQL>
SQL> INSERT ALL
2 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES (12345678901,'D',TO_DATE('10/12/13', 'mm/dd/yy'), TO_DATE('12/10/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">186037</VALUE><VALUE SID="2">186038</VALUE></LIST></ASSETTAGDATA>')
3 INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',TO_DATE('10/13/13', 'mm/dd/yy'), TO_DATE('10/14/13', 'mm/dd/yy'),'<ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">12VWX34567</VALUE></LIST></ASSETTAGDATA>')
4 select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> set numwidth 12
SQL> col val format a35
SQL> select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
  2         substr(listagg(','||extractvalue(value(x),'/VALUE'))
  3                  within group (order by extractvalue(value(x),'/VALUE/@SID')),
  4                2) val
  5  from ws_transactions, table(xmlsequence(extract(xmltype(Wtr_Req_XML), '//VALUE'))) x
  6  group by Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date
  7  /
WTR_SERVICE_TAG W WTR_SEND_DA WTR_RECEIVE VAL
--------------- - ----------- ----------- -----------------------------------
    12345678901 D 12-OCT-2013 10-DEC-2013 186037,186038
    23456789012 D 13-OCT-2013 14-OCT-2013 12XYZ34567,12WXY34567,12VWX34567

2 rows selected.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598441 is a reply to message #598436]

Mon, 14 October 2013 20:22

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

LISTAGG has delimiter as second parameter. Using that eliminates need for SUBSTR:

select  Wtr_Service_Tag,
        Wtr_Tran_Origin,
        Wtr_Send_Date,
        Wtr_Receive_Date,
        listagg(extractvalue(value(x),'/VALUE'),',')
                 within group (order by extractvalue(value(x),'/VALUE/@SID')) val
 from  ws_transactions, table(xmlsequence(extract(xmltype(Wtr_Req_XML), '//VALUE'))) x
 group by Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date
/

WTR_SERVICE_TAG W WTR_SEND_ WTR_RECEI VAL
--------------- - --------- --------- ----------------------------------
    12345678901 D 12-OCT-13 10-DEC-13 186037,186038
    23456789012 D 13-OCT-13 14-OCT-13 12XYZ34567,12WXY34567,12VWX34567

SQL>

But it could be done simpler:

select  Wtr_Service_Tag,
        Wtr_Tran_Origin,
        Wtr_Send_Date,
        Wtr_Receive_Date,
        (
         select listagg(val,',')
                 within group (order by val)
           from  xmltable(
                          '/ASSETTAGDATA/LIST/*'
                          passing xmltype(Wtr_Req_XML)
                          columns
                            val varchar2(20) path '/VALUE'
                         )
        ) val
 from  ws_transactions
/

WTR_SERVICE_TAG W WTR_SEND_ WTR_RECEI VAL
--------------- - --------- --------- -----------------------------------
    12345678901 D 12-OCT-13 10-DEC-13 186037,186038
    23456789012 D 13-OCT-13 14-OCT-13 12VWX34567,12WXY34567,12XYZ34567

SQL>

Although OP needs to answer if Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date combination is unique. And if it isn't, does OP want to combine them into a single row.
Also, OP needs to answer if concatenation of values can exceed 4000 bytes. If it can, LISTAGG needs to be replaced since it doesn't support clobs. XMLAGG can be used instead.

SY.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598597 is a reply to message #598441]

Tue, 15 October 2013 17:38

baggettms01
Messages: 15
Registered: October 2013

Junior Member

In response to the posted questions.

1) The unique identifier is simply the wtr_service_tag field
2) The 4000 byte limit might be an issue. Is the syntax for xmlagg the same or similar?

Thanks for all the help.

Report message to a moderator

Re: Looping and querying data from XML clob [message #598600 is a reply to message #598597]

Tue, 15 October 2013 19:26

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

1) Then use solution I posted.
2) XMLAGG is documented and there are plenty of examples to these forums and on the net.

SY.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599753 is a reply to message #598375]

Mon, 28 October 2013 11:34

baggettms01
Messages: 15
Registered: October 2013

Junior Member

I apologize for the long delay between my responses but this little project of mine got back-burnered for a little while due to other priorities at work. I am still having trouble getting this to work.

The XML that I provided in my 'test case' was just a small snip of a larger XML that I am pulling the data from. Attached to this post is a .txt file with a full example of the XML.

That being said, I am reposting the query portion of my 'test case' with a slight modification from what I originally posted in hopes of clearing up any confusion about what I am trying to do. The CREATE and INSERT statements I posted previously still apply with the exception of replacing the small XML snippet with the full XML in the attached file.

I am to extract list values from an XML CLOB field in our oracle database. I would like to be able to search by a single WTR_SERVICE_TAG (using the = operator) or multiples (using the in operator).

Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, 
    to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
  from ws_transactions
  Where Wtr_Service_Tag In ('20458749610')
    And Wtr_Req_Xml Like ('%CSM%')
  Order By Wtr_Receive_Date Desc

I really appreciate all the help I have received so far.

Attachment: XML Sample.txt
(Size: 2.41KB, Downloaded 2123 times)

Report message to a moderator

Re: Looping and querying data from XML clob [message #599757 is a reply to message #599753]

Mon, 28 October 2013 12:17

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I would like to be able to search by a single WTR_SERVICE_TAG (using the = operator) or multiples (using the in operator).

IN includes single value case.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599761 is a reply to message #599757]

Mon, 28 October 2013 12:40

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Michael: I don't understand what your statement means. I use the in operator quite frequently.

......where wtr_service_tag in ('1234ABC', 'ABC2345') I am not trying be a jerk, I really don't understand.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599762 is a reply to message #599761]

Mon, 28 October 2013 12:55

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can use "IN ('a lone value')" or "IN ('one','two','three','values')".
You don't need to use "=" when you have a single value, you can use IN.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599763 is a reply to message #599762]

Mon, 28 October 2013 13:11

baggettms01
Messages: 15
Registered: October 2013

Junior Member

OK. That makes sense. All that being said, Can you help me get this to work using the full sample XML I provided instead of the little snip from my original test case?

I have tried to apply all the potential solutions provided and am having no luck at all.

Thanks for your help and input.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599764 is a reply to message #599763]

Mon, 28 October 2013 13:38

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't understand the issue, you can add a WHERE to the query I gave, and I bet you can also do it with Solomon's ones.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599765 is a reply to message #599764]

Mon, 28 October 2013 13:42

baggettms01
Messages: 15
Registered: October 2013

Junior Member

My apologies. The Where isn't the issue, that was really an extra detail. I guess I didn't clearly state that.

The issue comes when I apply the provided solutions to the full xml clob instead of the little snip I originally included in my test case. This is why I attached a sample of the full xml previously.

When I try to apply any of the provided solutions to full xml, the 'val' column in the results is (null). I feel like it is related to the fact that the structure of the full xml is much more complex than the snip I orginally provided and the parsing isn't getting to the right data.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599782 is a reply to message #599765]

Mon, 28 October 2013 18:22

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

The problem is the path is incomplete for the new data. Instead of:

'/ASSETTAGDATA/LIST/*'

you need to either use the full path:

'/MessageEnvelope/PayloadList/PayloadGroup/Payload/Message/CFIDATA/SERVICELIST/SERVICE/ORDER/ASSETTAGDATA/LIST/*'

or use // to indicate the start of the sub-path:

'//ASSETTAGDATA/LIST/*'

Please see the demonstration below.

-- test data:

SCOTT@orcl12c> SET    NUMWIDTH	  15
SCOTT@orcl12c> COLUMN Wtr_Req_XML NEWLINE
SCOTT@orcl12c> SELECT *
  2  FROM   ws_transactions t
  3  WHERE  t.Wtr_Service_Tag In ('20458749610')
  4  AND    t.Wtr_Req_Xml LIKE ('%CSM%')
  5  /

WTR_SERVICE_TAG W WTR_SEND_DATE   WTR_RECEIVE_DAT
--------------- - --------------- ---------------
WTR_REQ_XML
------------------------------------------------------------------------------------------
    20458749610 D Sun 27-Oct-2013 Mon 28-Oct-2013
<MessageEnvelope>
  <PayloadList>
    <PayloadGroup Primary="1">
      <Payload Type="WorkOrderChange" Sequence="1">
        <Message>
          <WorkOrderChange>
            <MessageHeader>
              <MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID>
              <MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp>
              <SenderID>SENDER</SenderID>
              <ReceiverID>RECEIVER</ReceiverID>
              <MessageType>WOChange</MessageType>
              <CorrelationID/>
            </MessageHeader>
            <OrderID>12345678901</OrderID>
            <RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp>
            <RequestedChange>RELEASE</RequestedChange>
            <OrderPriority/>
            <ShippingInformation>
              <ShipCode/>
              <ServiceLevel/>
              <TransportationMode/>
              <CarrierCode/>
              <CarrierAccount/>
              <FOBCode/>
              <FreightTerms/>
              <CommercialResidential/>
            </ShippingInformation>
            <ShipToFacilityID/>
            <ShipToLocation>
              <CompanyName/>
              <ContactName/>
              <ContactPhone/>
              <ContactEmail/>
              <Address>
                <Line1/>
                <Line2/>
                <Line3/>
                <City/>
                <State/>
                <CountryCode/>
                <PostalCode/>
              </Address>
            </ShipToLocation>
            <DeliveryDate nil="true"/>
            <Instructions>
              <ShippingInstruction/>
              <PackingInstruction/>
            </Instructions>
          </WorkOrderChange>
        </Message>
      </Payload>
      <Payload Type="CSMOrderAttachment" Sequence="2">
        <Message>
          <CFIDATA>
            <SERVICELIST>
              <SERVICE TYPE="SYSTEM">
                <ORDER NUMBER="123456789" TIE="1" QUANTITY="2">
                  <ASSETTAGDATA>
                    <LIST NAME="AssetTag">
                      <VALUE SID="1">ABC1234</VALUE>
                      <VALUE SID="2">CBA4321</VALUE>
                    </LIST>
                  </ASSETTAGDATA>
                </ORDER>
              </SERVICE>
            </SERVICELIST>
          </CFIDATA>
        </Message>
      </Payload>
    </PayloadGroup>
  </PayloadList>
</MessageEnvelope>


1 row selected.

-- query with full path:

SCOTT@orcl12c> COLUMN vals FORMAT A32
SCOTT@orcl12c> SELECT  t.Wtr_Service_Tag,
  2  	     t.Wtr_Tran_Origin,
  3  	     t.Wtr_Send_Date,
  4  	     t.Wtr_Receive_Date,
  5  	     (SELECT LISTAGG (x.val,',') WITHIN GROUP (order by x.val)
  6  	      FROM   XMLTABLE
  7  		       ('/MessageEnvelope/PayloadList/PayloadGroup/Payload/Message/CFIDATA/SERVICELIST/SERVICE/ORDER/ASSETTAGDATA/LIST/*'
  8  			PASSING XMLTYPE (t.Wtr_Req_XML)
  9  			COLUMNS
 10  			  val  VARCHAR2(20)  PATH '/VALUE') x) vals
 11  FROM    ws_transactions t
 12  WHERE   t.Wtr_Service_Tag In ('20458749610')
 13  AND     t.Wtr_Req_Xml LIKE ('%CSM%')
 14  /

WTR_SERVICE_TAG W WTR_SEND_DATE   WTR_RECEIVE_DAT VALS
--------------- - --------------- --------------- --------------------------------
    20458749610 D Sun 27-Oct-2013 Mon 28-Oct-2013 ABC1234,CBA4321

1 row selected.

-- query using "//" to indicate where the sub-path starts:

SCOTT@orcl12c> COLUMN vals FORMAT A32
SCOTT@orcl12c> SELECT  t.Wtr_Service_Tag,
  2  	     t.Wtr_Tran_Origin,
  3  	     t.Wtr_Send_Date,
  4  	     t.Wtr_Receive_Date,
  5  	     (SELECT LISTAGG (x.val,',') WITHIN GROUP (order by x.val)
  6  	      FROM   XMLTABLE
  7  		       ('//ASSETTAGDATA/LIST/*'
  8  			PASSING XMLTYPE (t.Wtr_Req_XML)
  9  			COLUMNS
 10  			  val  VARCHAR2(20)  PATH '/VALUE') x) vals
 11  FROM    ws_transactions t
 12  WHERE   t.Wtr_Service_Tag In ('20458749610')
 13  AND     t.Wtr_Req_Xml LIKE ('%CSM%')
 14  /

WTR_SERVICE_TAG W WTR_SEND_DATE   WTR_RECEIVE_DAT VALS
--------------- - --------------- --------------- --------------------------------
    20458749610 D Sun 27-Oct-2013 Mon 28-Oct-2013 ABC1234,CBA4321

1 row selected.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599847 is a reply to message #599782]

Tue, 29 October 2013 07:11

baggettms01
Messages: 15
Registered: October 2013

Junior Member

Barbara thanks for your help.

Does the fact that I am using Oracle SQL developer matter?

The first query you provided correctly pulls the data. However, with the second query the vals column is null.

Also, the actual xml file has defined namespaces in it for a few of the nodes. I had stripped those out b/c SQL Developer does not like the colons in the xpath statement.

I am really stumped here. I appreciate your patience with me and I hope I am not being problematic. I am just trying to get this to work.

Report message to a moderator

Re: Looping and querying data from XML clob [message #599878 is a reply to message #599847]

Tue, 29 October 2013 14:25

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Quote:

Does the fact that I am using Oracle SQL developer matter?

Possibly. I don't use SQL Developer. I find that many such interfaces cause limitations and add another layer of opportunity for bugs and such. Test it in both SQL Plus and SQL Developer. If it works in SQL Plus, but not in SQL Developer, then you have a SQL Developer issue, in which case you should post the problem in the SQL Developer forum with a link to this thread.

Quote:

The first query you provided correctly pulls the data. However, with the second query the vals column is null.

It works for me, as demonstrated, so either you are doing something different without realizing it or there is something different on your system. It could be due to you using SQL Developer as you suggested. It might help to see a copy and paste of an actual run of what you did.

Quote:

Also, the actual xml file has defined namespaces in it for a few of the nodes. I had stripped those out b/c SQL Developer does not like the colons in the xpath statement.

Having different namespaces would be a problem. If you can automatically strip those out somehow that is the best way to go. Otherwise, you have to specify the possible namespaces as part of your query.

Report message to a moderator

Previous Topic:	Runtime error ORA-29515
Next Topic:	What is the default value of float and double datatype in Java?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 15:29:44 CST 2025