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:

FAT Fingered my DB name with a space character...

This wa an oops on my part. I created a new DB las week and I fatfingered the space character in it so now it's called: "DUMMY_FATFINGER " (Notice the space)

Other than recreating it how can I rename it correctly? Just clicking on it to rename it does not work. I get the Database already exists error.

Thankssp_rename?|||I forgot to mention it is on a SQL 2005 Server. Is sp_rename SQL 2005? I recall, I think I recall, sp_renamedb was SQL 2K?

Thanks for the quick reply.|||sp_renamedb should still work. everyone has to be logged out first though.|||I get the following error...
Msg 15032, Level 16, State 1, Procedure sp_renamedb, Line 36
The database 'HR_TRACKING' already exists. Specify a unique database name.

Obviously SQL knows the correct name, users are connecting to the correct name that does not have the space at the end. The problem is, when the backup job runs, it is getting the name with the space at the end. When I run the sp_renameDB the system knows already it has the correct name hence the error message above. It's a cosmeting naming thing somewhere in SQL.

Thanks|||Damn I get here BEFORE I get the email

OR

You could dump the db and rename it

Why do you care BTW?|||What Backup Job?

And let me ask this silly question

does the other bd actually exist?|||I care becaus when the backups kick off to backup to a disk device to a file, the filename to backup to comes out to be 'DBNAME_FATFINGER _backup_200710120945.bak'. It can't handle the space in the name and the backup job errors out.

Thanks|||BINGO!!!!

Renaming it twice did the trick!

I actually used the GUI to do it.