Select as XML then Insert into

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select as XML then Insert into

Author

Topic

KenA
Starting Member

28 Posts

Posted - 2006-05-29 : 16:51:24

Hi. I´m selecting data from a table as XML, without the XMLSCHEMA and then I need to insert the elements into a fild in another table. Ex:

select top 1 * from orgao for xml auto

How can I insert the result from that select into another table?

»»» Ken.A

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-29 : 17:09:04

Then why the ..... are you using xml?

__________________________
normalized in relationland

rockmoose

KenA
Starting Member

28 Posts

Posted - 2006-05-29 : 17:21:00

Because the .... other table will save it as a log table and in xml format, but actually as txt

»»» Ken.A

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-29 : 17:59:37

You could leave the data in tabular format in the log table,
and let the clients reading from the log table worry about the "for xml" part. (might save you some space too).

Other than that:

select '<xmlstuff><col1>' + ltrim(col1) + '</col1><col2>' + col2 + '</col2></xmlstuff>' from orgao

rockmoose

KenA
Starting Member

28 Posts

Posted - 2006-05-30 : 07:53:44

The issue is:

1. When I do a select like: select top 1 * from [tablename] for xml auto
The result is a string (in xml data) that for some reason, unknown to me, doesn´t let me to convert this xml string into some other type, eg: varchar ... in case I could do it, I would do something like:

2. Declare @myStr varchar(8000)
Set @myStr = convert(varchar,select top 1 * from [tablename] for xml auto)

3. And finally I could insert @myStr into the log table

Unfortunately I can´t do this!!!???

»»» Ken.A

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-30 : 10:43:50

It's not returned as a string, it's an "xmldocument".
Maybe you can use sp_xml_preparedocument and OPENXML.

Someone can help KenA?

______________________________________________________
Amazing, the energy it takes to make xml actually work

rockmoose

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-30 : 12:37:45

http://www.sqlxml.org/faqs.aspx?faq=104

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources