Fetching XML data from Oracle CLOB data field

I have one Oracle Table space which hav one CLOB type table field -- this field have XML data. Now What I doing with:
I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for XML data. Data is comming properly into MS sql 2000 DB.
Now I would to seprate XML field into another table in MS SQL TABe
Example
XML name,city,age,price field will copy to SQL field name,city..
is this possiable iwith MS SQL.
Arvind
Yes. You can use the OpenXML functionality to shred it (see books Online for
information). One caveat: You need to pass the document to T-SQL via a
stored proc using an NTEXT typed parameter if the data is larger than 8kB.
In SQL Server 2000 you can only do so by calling the stored proc from the
client/midtier and not within the server.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>I have one Oracle Table space which hav one CLOB type table field -- this
>field have XML data. Now What I doing with:
> I am fecting this data to MS SQL 2000 DB. I'd created one MEMO field for
> XML data. Data is comming properly into MS sql 2000 DB.
> Now I would to seprate XML field into another table in MS SQL TABe
> Example
> XML name,city,age,price field will copy to SQL field name,city..
> is this possiable iwith MS SQL.
> --
> Arvind
|||HI Michael,
Coould you please give one example so I can user OPEN XML. I'd already setup SQL package to get data from Oracle to SQL table. After that I would like to split XML data from SQL TEMP table to another SQL table.
Arvind
"Michael Rys [MSFT]" wrote:

> Yes. You can use the OpenXML functionality to shred it (see books Online for
> information). One caveat: You need to pass the document to T-SQL via a
> stored proc using an NTEXT typed parameter if the data is larger than 8kB.
> In SQL Server 2000 you can only do so by calling the stored proc from the
> client/midtier and not within the server.
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:5CAA29ED-8FD8-4D1C-BE53-58A31892C51E@.microsoft.com...
>
>
|||If you have the data inside a SQL table, then you unfortunately cannot
easily pass it through to sp_xml_preparedocument due to no support for
variables of type TEXT/NTEXT. So you either have to get the data out to
OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
the data from the Oracle database passed into the stored proc directly.
There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...[vbcol=seagreen]
> HI Michael,
> Coould you please give one example so I can user OPEN XML. I'd already
> setup SQL package to get data from Oracle to SQL table. After that I
> would like to split XML data from SQL TEMP table to another SQL table.
>
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote:
|||How DO I pased XML data from oracle to MS SQL thru store proc.
My Oracle Table space have 5 field and 1 clob filed for XML.
--
Arvind
"Michael Rys [MSFT]" wrote:

> If you have the data inside a SQL table, then you unfortunately cannot
> easily pass it through to sp_xml_preparedocument due to no support for
> variables of type TEXT/NTEXT. So you either have to get the data out to
> OLEDB/ADO/ADO.net and call back into the database using a stored proc or get
> the data from the Oracle database passed into the stored proc directly.
> There are some dynamic SQL workarounds as well (see http://www.sqlxml.org).
> Best regards
> Michael
> "Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
> news:26E3A058-FD44-4C21-B756-AAFB606EC040@.microsoft.com...
>
>
|||You would write a midtier program (for example using the Oracle OLEDB
provider) and retrieve the information and pass it through ADO to a stored
proc where you have a parameter per field and an NTEXT parameter for the
XML.
Best regards
Michael
"Arvind Saxena" <ArvindSaxena@.discussions.microsoft.com> wrote in message
news:2F32C6BE-BD3E-4F29-AE93-66DA5CEC914A@.microsoft.com...[vbcol=seagreen]
> How DO I pased XML data from oracle to MS SQL thru store proc.
> My Oracle Table space have 5 field and 1 clob filed for XML.
>
> --
> --
> Arvind
>
> "Michael Rys [MSFT]" wrote: