Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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.
0
dthoms000
Asked:
dthoms000
  • 3
  • 2
1 Solution
 
keyuCommented:
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
0
 
Jared_SCommented:
To expand a little, the following code will put each unique record in the table inside of a node named <row> with a root of <table>

The first select displays the results of the for xml query.

The second select gives you the number of records (count of <row>)

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

select @result

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

Open in new window


Just replace "select * from myTable" with the query that meets your needs, and the rest of the code in place.
0
 
dthoms000Author Commented:
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)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dthoms000Author Commented:
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)
0
 
Jared_SCommented:
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?
0
 
dthoms000Author Commented:
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now