Home » Developer & Programmer » JDeveloper, Java & XML » XML From BLOB (Oracle 10g)
XML From BLOB [message #451606] Thu, 15 April 2010 12:32 Go to next message
jeffreypry
Messages: 8
Registered: April 2010
Junior Member
Hey,

I have an XML file stored in an Oracle 10g database as a BLOB. I was wondering if anyone knew how to use extract to get the values out of the file. I just want to get group name and every user in the group. I have included a copy of the XML file below for your review. I have tried to extract XML from it in the past but it seems the metadata: (colon) messes something up. Any help would be much appreciated.

Thank you!

Jeffrey Kevin Pry
<metadata:Group xmlns:xsd="(removed as I cant post links yet)">
<id>20</id>
<groupName>Users</groupName>
<nameSpace>Domain</nameSpace>
<description></description>
<userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
<userName>user1</userName>
<nameSpace>Domain</nameSpace>
</userRef>
<userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
<userName>user2</userName>
<nameSpace>Domain</nameSpace>
</userRef>
</metadata:Group>

[Updated on: Thu, 15 April 2010 12:33]

Report message to a moderator

Re: XML From BLOB [message #451611 is a reply to message #451606] Thu, 15 April 2010 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post what you tried and also the page target of the link you didn't post otherwise we can't try some queries.

Regards
Michel

Report message to a moderator

Re: XML From BLOB [message #451655 is a reply to message #451606] Fri, 16 April 2010 00:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Just a remark: since XML-files only contain character data, you could/should store it in a CLOB, rather than a BLOB.

Report message to a moderator

Re: XML From BLOB [message #451744 is a reply to message #451606] Fri, 16 April 2010 09:52 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
As a first example use:
WITH xml_data AS 
(SELECT  XMLTYPE(
  '<metadata xmlns="urn:REMOVED" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <id>20</id>
    <groupName>Users</groupName>
    <nameSpace>Domain</nameSpace>
    <description></description>
    <userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
      <userName>user1</userName>
      <nameSpace>Domain</nameSpace>
    </userRef>
    <userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
      <userName>user2</userName>
      <nameSpace>Domain</nameSpace>
    </userRef>
  </metadata>') xml_data FROM dual)
SELECT extractvalue(column_value,'//userName',' xmlns="urn:REMOVED"') userName 
FROM xml_data, 
TABLE (XMLSEQUENCE (EXTRACT(xml_data,'//userName',' xmlns="urn:REMOVED"')));

USERNAME
-----------------------
user1                                                            user2


And anyway read the manual XML

[Updated on: Fri, 16 April 2010 10:04] by Moderator

Report message to a moderator

Re: XML From BLOB [message #451751 is a reply to message #451744] Fri, 16 April 2010 10:05 Go to previous messageGo to next message
jeffreypry
Messages: 8
Registered: April 2010
Junior Member
@Frank
Quote:
Just a remark: since XML-files only contain character data, you could/should store it in a CLOB, rather than a BLOB.

Unfortunately I am working on integrating with an existing system and changing the column type is not an option. Thanks for the advice though.

@_jum
My issue with using that example (which I really appreciate) is that the first metadata line is horrendously long... I trimmed it down to fit in the post the first time bu looking at your example it look as though it needs to be involved in the query somehow. 
<metadata:Group xmlns:common="http://www.example.com/pcsf/common" xmlns:usermanagement="http://www.example.com/pcsf/usermanagement" xmlns:domainservice="http://www.example.com/pcsf/domainservice" xmlns:logservice="http://www.example.com/pcsf/logservice" xmlns:domainbackup="http://www.example.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.example.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.example.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.example.com/pcsf/alertservice" xmlns:licenseusage="http://www.example.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.example.com/pcsf/webserviceshub"  xsi:type="metadata:Group"  objVersion="1.1.19">

How do I insert this actual first line of the XML file into your example? Also, what is the urn:REMOVED? I need it to work for metadata:Group. The colon seems to be an issue.

Thank you so much!

Jeffrey Kevin Pry

[Updated on: Fri, 16 April 2010 10:23]

Report message to a moderator

Re: XML From BLOB [message #451759 is a reply to message #451751] Fri, 16 April 2010 10:22 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Try it, its simple:
WITH xml_data AS 
(SELECT  XMLTYPE(
  '<metadata:Group xmlns:common="http://www.js.com/pcsf/common" xmlns:usermanagement="http://www.js.com/pcsf/usermanagement" xmlns:domainservice="http://www.js.com/pcsf/domainservice" xmlns:logservice="http://www.js.com/pcsf/logservice" xmlns:domainbackup="http://www.js.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.js.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.js.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.js.com/pcsf/alertservice" xmlns:licenseusage="http://www.js.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.js.com/pcsf/webserviceshub"  xsi:type="metadata:Group"  objVersion="1.1.19">
     <id>20</id>
    <groupName>Users</groupName>
    <nameSpace>Domain</nameSpace>
    <description></description>
    <userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
      <userName>user1</userName>
      <nameSpace>Domain</nameSpace>
    </userRef>
    <userRef xsi:type="metadata:UserRef" objVersion="1.1.19">
      <userName>user2</userName>
      <nameSpace>Domain</nameSpace>
    </userRef>
  </metadata:Group>') xml_data FROM dual)
SELECT extractvalue(column_value,'//userName') userName 
FROM xml_data, 
TABLE (XMLSEQUENCE (EXTRACT(xml_data,'//userName')));

USERNAME
---------------
user1
user2

The "urn:REMOVED" came from your very first post "xmlns:xsd="(removed as I cant post links yet)"
Best luck _jum!

[Updated on: Fri, 16 April 2010 10:26]

Report message to a moderator

Re: XML From BLOB [message #451760 is a reply to message #451759] Fri, 16 April 2010 10:24 Go to previous messageGo to next message
jeffreypry
Messages: 8
Registered: April 2010
Junior Member
Thanks _jum... You're amazing! Exactly what I was looking for...

Thanks again!

Jeffrey Kevin Pry

Report message to a moderator

Re: XML From BLOB [message #451762 is a reply to message #451759] Fri, 16 April 2010 10:46 Go to previous messageGo to next message
jeffreypry
Messages: 8
Registered: April 2010
Junior Member
One last thing, how would I select the group name and then all of the users. So ideally I would like to have the results be the following:

GroupName userName
Users user1
Users user2

or just

Users
user1
user2

Thanks again!

[Updated on: Fri, 16 April 2010 11:39]

Report message to a moderator

Re: XML From BLOB [message #451939 is a reply to message #451762] Mon, 19 April 2010 01:22 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
Did you read the link, where is your problem?
Try to extractvalue groupName from xml_data...

Best luck
_jum!

Report message to a moderator

Previous Topic: JDeveloper-Named Iterator
Next Topic: Multiple Version are being made in Oracle for certain SQL's due to bind variable mismatchwith Oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 00:55:40 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.