select distinct values on XMLType
I've got a table that has a XMLType field. The table is created and loaded using the following DDL/DML:
CREATE TABLE T_ECO_test_LOG
(
SECID NUMBER NOT NULL,
LOG_ATTRIBUTES SYS.XMLTYPE
)
INSERT INTO t_eco_test_log VALUES
( 1, XMLType(
'<attributes>
<attribute>
<name>remoteAddress</name>
<value>180.201.106.130</value>
</attribute>
<attribute>
<name>domain</name>
<value>BSI_US</value>
</attribute>
</attributes>'));
INSERT INTO t_eco_test_log VALUES
( 2, XMLType(
'<attributes>
<attribute>
<name>user</name>
<value>xxxx</value>
</attribute>
<attribute>
<name>domain</name>
<value>BSI_US</value>
</attribute>
</attributes>'));
I wan开发者_StackOverflow中文版t to get the different values in /attributes/attribute/name, in rows; So with data O would like to get:
remoteAddress
domain
user
So far I've tried the following query:
select extractValue(value(x),'/attributes/attribute/name')
from t_eco_log,
table(xmlsequence(extract(log_attributes,'/attributes')) )x
But I get the following message :
ORA-19025: EXTRACTVALUE returns value of only one node
If I use
select extract(value(x),'/attributes/attribute/name')
from t_eco_log,
table(xmlsequence(extract(log_attributes,'/attributes')) )x
I got a XML result which contains :
<name>remoteAddress</name><name>domain</name>
But I would like to get them as rows, how can I do that?
TIA
Something like :
with x1 as (select xmltype('<attributes>
<attribute>
<name>remoteAddress</name>
<value>180.201.106.130</value>
</attribute>
<attribute>
<name>domain</name>
<value>BSI_US</value>
</attribute>
</attributes>') x2 from dual)
select extract(value(x3),'/attribute/name')
from x1,
table(xmlsequence(extract(x2,'/attributes/*')) ) x3
If you supply CREATE TABLE and INSERT, then it is easier to give a precise SQL
I got it. Base on what Gary put:
with x1 as (select log_attributes x2 from t_eco_test_log)
select distinct(extractValue(value(x3),'/attribute/name'))
from x1,
table(xmlsequence(extract(x2,'/attributes/*')) ) x3
Thank you!
精彩评论