OraFAQ Forum: JDeveloper, Java & XML » Extracting values from the XML string

Home » Developer & Programmer » JDeveloper, Java & XML » Extracting values from the XML string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extracting values from the XML string [message #563299]

Sat, 11 August 2012 14:53

Rithy03
Messages: 1
Registered: August 2012
Location: Ashburn

Junior Member

Hi,

I would like to extract only the values from the XML string
The xml string looks like

<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>

And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA
I need to extract only the values from the XMl string using sql
Could anyone please help

Report message to a moderator

Re: Extracting values from the XML string [message #563300 is a reply to message #563299]

Sat, 11 August 2012 17:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

What does "I would like to extract only the values from the XML string" have to do with Oracle RDBMS?

Report message to a moderator

Re: Extracting values from the XML string [message #563302 is a reply to message #563299]

Sat, 11 August 2012 17:18

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Rithy03 wrote on Sat, 11 August 2012 15:53

The xml string looks like

It doesn't look like a well-formed XML:

SQL> select xmltype('
  2  <addressId>1</addressId>
  3  <addressSource xsi:nil="true"/>
  4  <addressInfoName xsi:nil="true"/>
  5  <addressLine1>245 Murray Ln SW Bldg 14</addressLine1>
  6  <addressLine2 xsi:nil="true"/>
  7  <addressLine3 xsi:nil="true"/>
  8  <addressLine4 xsi:nil="true"/>
  9  <phoneNumber xsi:nil="true"/>
 10  <phoneNumberExt xsi:nil="true"/>
 11  <city>Washington</city>
 12  <county xsi:nil="true"/>
 13  <stateOrProvince>DC</stateOrProvince>
 14  <ZIPCode>20528-1002</ZIPCode>
 15  <country>USA</country>
 16  <congressionalDistrict xsi:nil="true"/>
 17  </address>'
 18  ) from dual
 19  /
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00245: extra data after end of document
Error at line 3
ORA-06512: at "SYS.XMLTYPE", line 301
ORA-06512: at line 1



no rows selected

SQL>

It has end tag </address> but no <address> tag. It references namespace XSI:NIL without declaring it and possibly has more issues.

SY.

[Updated on: Sat, 11 August 2012 17:19]

Report message to a moderator

Re: Extracting values from the XML string [message #572963 is a reply to message #563299]

Wed, 19 December 2012 01:11

vikramschandel
Messages: 4
Registered: April 2012
Location: Hyderabad

Junior Member

For extracting XML values you can take reference from below example:

SELECT EXTRACTVALUE (COLUMN_VALUE, '/details/emp_id') "Employee_ID",
EXTRACTVALUE (COLUMN_VALUE, '/details/emp_name') "Employee_Name",
EXTRACTVALUE (COLUMN_VALUE, '/details/emp_age') "Employee_Age",
EXTRACTVALUE (COLUMN_VALUE, '/details/emp_dept') "Employee_department"
FROM TABLE
(XMLSEQUENCE
(XMLTYPE
('<?xml version="1.0"?>
<employee>
<details>
<emp_id>1001</emp_id>
<emp_name>xmloracle</emp_name>
<emp_age>23</emp_age>
<emp_dept>IT</emp_dept>
</details>
</employee>'
).EXTRACT ('/employee/details')
)
) t;

Output:
Employee_ID Employee_Name Employee_Age Employee_department
1001 xmloracle 23 IT

In your case the xml is not well formed as in earlier reply also it has been mentioned about <address> tag.
Can you post well formed xml so that will be able to help more.
Meanwhile you can take reference of above mentioned statements also.

Report message to a moderator

Re: Extracting values from the XML string [message #572969 is a reply to message #572963]

Wed, 19 December 2012 01:30

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why it is not welformed XML?
If it was not, Oracle would not work with it!

Regards
Michel

Report message to a moderator

Previous Topic:	Regarding schema validation and dbms_xmlparser, dbms_xmldom
Next Topic:	XML parsing in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:29:35 CST 2025