asked on

sql xml

want to select all columns from each row in table A into an xml column in table b ¿¿and then use select to return the rows in the table b xml column. II do not want to have to have knowledge of the table A schema. Using this for quick backups.

keyu

Hi..

"For XML" command will help you to store xml vales..

refer below link might helps you..

http://msdn.microsoft.com/en-us/library/ms345137(v=SQL.90).aspx

To read xml you can go for "openxml"

refer below link might helps you..

http://msdn.microsoft.com/en-us/library/ms186918.aspx

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dthoms000

ASKER

Thanks. That snippet accomplished the first part of my question. The second part is more difficult for me. I want to be able to do the follwing without knowing the structure.
/table/row are always the same but the rest of thecolums are determined by the table i am archiving and i will not know that so i want code to dynamically return the result w/o specifically coding for each table i want to archive rows from

declare @result as xml
set @result = (select * from #s
for xml raw, root('table'), ELEMENTS)

select @result

declare @count as int
select @count = @result.value('count(/table/row)','int')
select @count as [count]

select col.value('data(recid[1])', 'int') as column1
, col.value('data(a[1])', 'varchar(max)') as column2
from @result.nodes('/table/row') tbl(col)

dthoms000

ASKER

this sets up #s...

drop table #h
drop table #s
create table #s(recid int, a varchar(1), b datetime, c decimal(19,5))
create TABLE #h( ID INT identity(1,1),rowdata XML )

INSERT INTO #s
select * from
(values (1,'a','04/04/1959',12.3456),
(2,'b','04/05/1959',654.321)) as x(recid,a,b,c)

Jared_S

This is as close as I've been able to come to what you're describing.

select x.y.value('local-name(.)','varchar(50)') as [fName], 
x.y.value('.','varchar(50)') as [fValue]
from @result.nodes('//*') x(y)

Open in new window

Why not count them from your database prior to generating the xml?

dthoms000

ASKER

Thanks again you have good knowledge of sql xml. I will experiment with your last proposal. There must be some confusion though as I am not trying to count anything. I want to save row data from one table to an xml column in another table then query the xml data later without having to know the table structures in either direction.