Home » Developer & Programmer » JDeveloper, Java & XML » Looping and querying data from XML clob
Looping and querying data from XML clob [message #598375] |
Mon, 14 October 2013 07:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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.
How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
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
|
|
|
|
Re: Looping and querying data from XML clob [message #598377 is a reply to message #598375] |
Mon, 14 October 2013 08:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3273 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 #598413 is a reply to message #598398] |
Mon, 14 October 2013 10:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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.
|
|
|
Re: Looping and querying data from XML clob [message #598417 is a reply to message #598413] |
Mon, 14 October 2013 10:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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;
|
|
|
Re: Looping and querying data from XML clob [message #598425 is a reply to message #598421] |
Mon, 14 October 2013 11:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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
|
|
|
|
Re: Looping and querying data from XML clob [message #598432 is a reply to message #598430] |
Mon, 14 October 2013 11:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
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
|
|
|
Re: Looping and querying data from XML clob [message #598433 is a reply to message #598432] |
Mon, 14 October 2013 12:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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;
|
|
|
|
Re: Looping and querying data from XML clob [message #598435 is a reply to message #598434] |
Mon, 14 October 2013 12:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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;
|
|
|
Re: Looping and querying data from XML clob [message #598436 is a reply to message #598435] |
Mon, 14 October 2013 14:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
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.
|
|
|
Re: Looping and querying data from XML clob [message #598441 is a reply to message #598436] |
Mon, 14 October 2013 20:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3273 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.
|
|
|
|
|
Re: Looping and querying data from XML clob [message #599753 is a reply to message #598375] |
Mon, 28 October 2013 11:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e3b2f24397fc013b072c8f9161ad8d1a?s=64&d=mm&r=g) |
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.
|
|
|
|
|
|
|
|
|
Re: Looping and querying data from XML clob [message #599782 is a reply to message #599765] |
Mon, 28 October 2013 18:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9095 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.
|
|
|
|
Re: Looping and querying data from XML clob [message #599878 is a reply to message #599847] |
Tue, 29 October 2013 14:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9095 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.
|
|
|
Goto Forum:
Current Time: Sun May 19 08:02:04 CDT 2024
|