Home » Developer & Programmer » JDeveloper, Java & XML » Extracting values from the XML string
Extracting values from the XML string [message #563299] Sat, 11 August 2012 14:53 Go to next message
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
Re: Extracting values from the XML string [message #563300 is a reply to message #563299] Sat, 11 August 2012 17:06 Go to previous messageGo to next message
BlackSwan
Messages: 22679
Registered: January 2009
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?
Re: Extracting values from the XML string [message #563302 is a reply to message #563299] Sat, 11 August 2012 17:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1993
Registered: January 2010
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 Go to previous messageGo to next message
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.
Re: Extracting values from the XML string [message #572969 is a reply to message #572963] Wed, 19 December 2012 01:30 Go to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why it is not welformed XML?
If it was not, Oracle would not work with it!

Regards
Michel
Previous Topic: Regarding schema validation and dbms_xmlparser, dbms_xmldom
Next Topic: XML parsing in Oracle
Goto Forum:
  


Current Time: Wed Aug 20 10:11:40 CDT 2014

Total time taken to generate the page: 0.09170 seconds