Re: XMLTYPE in Oracle9i/ XMLs with name-value pairs

"Juergen" <juergen.gerlinger_at_web.de> wrote in message news:dc56b85a.0405040738.5d71748c_at_posting.google.com...
> Hi,
>
> I have created the following table:
>
> CREATE TABLE Test_XML (
> XMLTypeID NUMBER(10),
> DetailsXML XMLTYPE)
> XMLTYPE DetailsXML STORE AS OBJECT RELATIONAL;
>
> and have stored the following XML for XMLTypeID=1 in the column DetailsXML
>
> <?xml version="1.0" encoding="ISO-8859-15"?>
> <ArticleDetails>
> <Technical Name="Name1" Value="Test"/>
> <Technical Name="Name2" Value="Y"/>
> <Technical Name="Name3" Value="Y"/>
> </ArticleDetails>
>
> 1.) What's the easiest way to select the Value for Name="Name1"?
> 2.) How can I update Value for Name="Name1" in this XML?
>
> Cheers
> Juergen

It truly depends on what patchset (if any) that you are on. And the syntax does vary because of the w3c standard that Oracle is trying to implement. Please check documentation:

But:
1. something like select
extract(x.detailsXML,'/ArticleDetails/Technical[Name1]').getstringval() from test_XML x.
The xpath component to the select is the key. And you may need to fiddle with it to get it right.

2. update test_XML set details =
updatexml(details,'/ArticleDetails/Technical[Name1].text()',newvalue) where record_id = some_value.

You can use the DOM parser or the SAX parser. And use PL/SQL or Java. But I much prefer SQL.
And it is fast. I just went through some painful experiences and we finally got it right under 9.2.0.5.
Our problem was with CLOBs and special characters. Be careful if you need to update and XMLtype with a value like O'reilly.

Good luck,
Tim Received on Wed May 05 2004 - 08:24:52 CDT