Database Software

Hi,

I have some xml in the formatt:
<?xml version="1.0" encoding="UTF-8"?>
<csg>
<ecrminput id="1">
<xml id="001" title="CustomerId">CustomerId11</xml>
<xml id="002" title="ContactId">ContactId11</xml>
<xml id="003" title="CustomerNo">CustomerNo11</xml>
<xml id="004" title="Branch">Branch11</xml>
<xml id="500" title="Attribute">Attribute11</xml>
<xml id="500" title="Attribute">Attribute11a</xml>
<xml id="500" title="Attribute">Attribute11b</xml>
</ecrminput>
<ecrminput id="2">
<xml id="0011" title="CustomerId">CustomerId22</xml>
<xml id="0021" title="ContactId">ContactId22</xml>
<xml id="0031" title="CustomerNo">CustomerNo2</xml>
<xml id="0041" title="Branch">Branch2</xml>
<xml id="5001" title="Attribute">Attribute22</xml>
<xml id="5002" title="Attribute">Attribute22a</xml>
<xml id="5003" title="Attribute">Attribute22b</xml>
</ecrminput>
</csg>

I have loaded this into an xmltype and am now trying to extract out the data.

Does anyone know why the 2 select statements return different results?

select eid, id, title, value
from xml_talent_tickets,
XMLTABLE
(
'/csg/ecrminput'
passing OBJECT_VALUE
columns
eid varchar2(200) path '@id',
xml XMLTYPE path 'xml'
) ec,
XMLTABLE
(
'/xml'
passing ec.xml
columns
ID varchar2(200) path '@id',
TITLE VARCHAR2(64) path '@title',
VALUE VARCHAR2(64) path '.'
);

returns this:

EID ID TITLE VALUE
1 1 CustomerId CustomerId11
1 2 ContactId ContactId11
1 3 CustomerNo CustomerNo11
1 4 Branch Branch11
1 500 Attribute Attribute11
1 500 Attribute Attribute11a
1 500 Attribute Attribute11b
2 11 CustomerId CustomerId22
2 21 ContactId ContactId22
2 31 CustomerNo CustomerNo2
2 41 Branch Branch2
2 5001 Attribute Attribute22
2 5002 Attribute Attribute22a
2 5003 Attribute Attribute22b

which is what i want and

select eid, id, title, value
from xml_talent_tickets,
XMLTABLE
(
'for $i in /csg/ecrminput return $i'
passing OBJECT_VALUE
columns
eid varchar2(200) path '@id',
xml XMLTYPE path 'xml'
) ec,
XMLTABLE
(
'for $i in /xml return $i'
passing ec.xml
columns
ID varchar2(200) path '@id',
TITLE VARCHAR2(64) path '@title',
VALUE VARCHAR2(64) path '.'
)

returns this:

EID ID TITLE VALUE
1 1 CustomerId CustomerId11
1 2 ContactId ContactId11
1 3 CustomerNo CustomerNo11
1 4 Branch Branch11
1 500 Attribute Attribute11
1 500 Attribute Attribute11a
1 500 Attribute Attribute11b
2 1 CustomerId CustomerId11
2 2 ContactId ContactId11
2 3 CustomerNo CustomerNo11
2 4 Branch Branch11
2 500 Attribute Attribute11
2 500 Attribute Attribute11a
2 500 Attribute Attribute11b

which is not what i want. as you can see the details in the ID, title and value field all belong to the first ec.xml type.

I want to sue the 'for *** in' route as i need to add a where clause.

Any ideas what i need to do to the second query to return the results from the first??