DevOps, CI/CD and Automation

Hi ALL,

Good Morning to all, i have problem in fetching a value from a xml tag. I have created a xml schema based on the schema i have created a xmltype table and inserted a value to the table. When i am trying to fetch a value from a particular tag i am unable to do so.. Kindly help me to solve this. Here by i am posting all the workings i have done...

I am using the following client:
-----------------------------------

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 31 11:44:59 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


////////////////////////////////// XML Schema ///////////////////////

begin
dbms_xmlschema.registerSchema(
'http://www.oradev.com/chipsxml.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oradev.com/chipsxml.xsd"
xmlns:samp="http://www.oradev.com/chipsxml.xsd"
version="1.0">

<element name="Field1">
<complexType>
<sequence>
<element name="UTI">
<complexType>
<sequence>
<element name = "U01" type = "string"/>
<element name = "U02" type = "string"/>
<element name = "U03" type = "string"/>
<element name = "U03a" type = "string"/>
<element name = "U03b" type = "string"/>
<element name = "U03c" type = "string"/>
<element name = "U04" type = "string"/>
<element name = "U05" type = "string"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>',
TRUE, TRUE, FALSE, FALSE);
end;


////////////////////////// Table which has multiple Column //////////////////////////

CREATE TABLE chipsxmltable1 (
id number, XMLDATA XmlType)
XMLTYPE XMLDATA STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://www.oradev.com/chipsxml.xsd"
ELEMENT "Field1";


///////////////////////////////// Insert Query in chipsxmltable //////////////////////////

INSERT INTO chipsxmltable VALUES(
xmltype.createxml('<?xml version="1.0"?>
<samp:Field1 xmlns:samp="http://www.oradev.com/chipsxml.xsd" >
<UTI>
<U01>No</U01>
<U02>Y</U02>
<U03>Y</U03>
<U03a>Y</U03a>
<U03b>Y</U03b>
<U03c>Y</U03c>
<U04>Y</U04>
<U05>Y</U05>
</UTI>
</samp:Field1>'));


To show the data as a field with structure:
--------------------------------------------

1. Query:
----------
Select * from chipsxmltable1;

Output:
-------


ID XMLDATA
---------- -----------------------------------------------------------------
1 <?xml version="1.0"?>
<samp:Field1 xmlns:samp="http://www.oradev.com/chipsxml.xsd">
<UTI>
<U01>No</U01>
<U02>No</U02>
<U03>Y</U03>
<U03a>Y</U03a>
<U03b>Y</U03b>
<U03c>Y</U03c>
<U04>Y</U04>
<U05>Y</U05>
</UTI>
</samp:Field1>


2. Query: (Both the query displays the same Output)
----------

SELECT X.xmldata.getClobVal() "XMLDATA" FROM chipsxmltable1 X;

select extract(XMLDATA, '/Field1').getstringval() "XMLDATA" from chipsxmltable1 x;


Output:
--------

XMLDATA
-----------------------------------------------------------------
<?xml version="1.0"?>
<samp:Field1 xmlns:samp="http://www.oradev.com/chipsxml.xsd">
<UTI>
<U01>No</U01>
<U02>No</U02>
<U03>Y</U03>
<U03a>Y</U03a>
<U03b>Y</U03b>
<U03c>Y</U03c>
<U04>Y</U04>
<U05>Y</U05>
</UTI>
</samp:Field1>


To show the data as a single string without structure using "getstringval()":
---------------------------------------------------------------------------------

3. Query
---------

select extract(XMLDATA, '//text()').getstringval() "CHIPS - XML" from chipsxmltable1 x;

OUtput:
-------

CHIPS - XML
---------------------
NoNoYYYYYY


To show the data as a single string without structure using "getclobval()":
---------------------------------------------------------------------------------

4.Query
-------

select extract(XMLDATA, '//text()').getClobVal() "CHIPS - XML" from chipsxmltable1 x;

Output:
--------

CHIPS - XML
-----------------
NoNoYYYYYY


To show the data in a particular tag with/Without structure (Which is not working) using "EXTRACT" function:
-------------------------------------------------------------------------------------------------------------

6.Query:
---------

select extract(XMLDATA, '/Field1/text()').getstringval() "XMLDATA" from chipsxmltable1 x;

select extract(XMLDATA, '/Field1/UTI').getstringval() "XMLDATA" from chipsxmltable1 x;

select extract(XMLDATA, '/Field1/UTI/U01').getstringval() "XMLDATA" from chipsxmltable1 x;

select extract(XMLDATA, '/Field1/UTI/U01/text()').getstringval() "XMLDATA" from chipsxmltable1 x;


Output:
--------

CHIPS - XML
---------------------------------------



The above queries are not fetching the value.



To show the data in a particular tag with/Without structure (Which is not working) using "EXTRACTVALUE" function:
------------------------------------------------------------------------------------------------------------------

7. Query:
----------

select extractValue(XMLDATA, '/Field1/UTI') "XMLDATA" from chipsxmltable1 x;

select extractValue(XMLDATA, '/Field1/UTI/U01') "XMLDATA" from chipsxmltable1 x;


Output:
--------

X
-



The above queries are not fetching the value.


My question is:
--------------------
How to fetch values from xml tag when the value are inserted through xml schema?


Apologies if the description is not clear. Kindly let me know if further details are needed. Many thanks for your help.

Very best regards,
Godwin Jebakumar C.V.