I have this XML.
<a>
<b>b1</b>
<c>c1</c>
<b>b2</b>
<c>c2</c2>
</a>
I want to be able to extract the values of elements 'b' and 'c' using PL\SQL. I'm using Oracle 10g.
So far I have this,
SELECT XML.b
, XML.c
FROM XMLTable (
'/a' PASSING p_xml
COLUMNS
b VARCHAR(2) PATH 'b/.'
, c VARCHAR(2) PATH 'c/.'
) XML
But I keep getting this error:
19279. 00000 - "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
Then I tried this:
SELECT XML.b
, XML1.c
FROM XMLTable (
'/a/b' PASSING p_xml
COLUMNS
b VARCHAR(2) PATH '.'
) XML,
XMLTable (
'/a/c' PASSING p_xml
COLUMNS
c VARCHAR(2) PATH '.'
) XML1
But the results were:
b1,c1
b1,c2
b2,c1
b2,c2
When I want only: b1 c1 b2 c2
Can you guys help me out?
I didnt run it but as far as i see below code should work ;
DECLARE
vs_Xml VARCHAR2(32000):= '<INPUT>
<A>
<B>1</B>
</A>
<A>
<B>2</B>
</A>
</INPUT>';
vx_ParameterList XMLTYPE;
vx_Parameter XMLTYPE;
vn_ParameterIndex NUMBER;
vs_Key VARCHAR2(64);
vs_XPath VARCHAR2(255);
vs_Value VARCHAR2(10000);
BEGIN
vx_ParameterList := xmltype(vs_Xml);
vn_ParameterIndex := 1;
vs_XPath := '/INPUT/A';
WHILE vx_ParameterList.existsNode(vs_XPath || '[' || vn_ParameterIndex || ']') = 1 LOOP
vx_Parameter := vx_ParameterList.extract(vs_XPath || '[' || vn_ParameterIndex || ']');
vs_Value := vx_Parameter.extract('//B/text()').GetStringVal();
vn_ParameterIndex := vn_ParameterIndex + 1;
dbms_output.put_line(vs_Value);
END