For XML: create elements using cell values

Hi All,
If have a sql table with 2 columns and 2 rows with values
[["col1row1","col2row1"],["col1row2","col2row2"]].

Using t-SQL with "for xml"
How can i create a xml where the cell values (not column names) appear
as elements?
eg:
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>

Thanks,

slyi

-- It can be done, but remember that you will have to
-- escape all the XML yourself

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

select 1 as Tag,
null as Parent,
'<'+col1+'>'+col2+'</'+col1+'>' as [TestNode!1!!xml]
from #test

order by Tag,[TestNode!1!!xml]
for xml explicit

drop table #test

Thanks thats exactly what i needed to know

On closer examination this wont work it gives

<TestNode><col1row1>col2row1</col1row1></TestNode>
<TestNode><col1row2>col2row2</col1row2></TestNode>
while i need something like
<TestNode>
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>
</TestNode>

Unless someone else knows better, you're out of luck. Perhaps
you could look at redesigning the XML you are generating
and then apply an XSL transformation at the client.

Thanks Mark. Could i create a temp table, with the cell values as
columns and build a sql xml query or loop from there?
Although im not too sure if that would work, very efficiently?

(adrianca@gmail.com) writes:[color=blue]
> Thanks Mark. Could i create a temp table, with the cell values as
> columns and build a sql xml query or loop from there?
> Although im not too sure if that would work, very efficiently?[/color]

I can't see that you can do this in SQL 2000 at all. Well, you can
build an nvarchar string that has the XML, and forego FOR XML
altogether, but if you exceed 4000 characters you lose anyway.

I think you need to build this document client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

--something like below is what i was thinking but is it efficent?
--as the client side asp code works out very slow thats why i want to
do it on the sql server if possible

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

create table #xmltree( xmlblob text)
INSERT INTO #xmltree VALUES ('<table>')

Declare @sqlq varchar(4000)
DECLARE @textptr varbinary(16)
DECLARE @bigtext varchar(8000)
DECLARE @textlen int
DECLARE @col1 varchar(32), @col2 varchar(32)

SELECT @textptr=TEXTPTR(xmlblob) FROM #xmltree

DECLARE tst_cursor CURSOR FOR select * from #test
OPEN tst_cursor
FETCH NEXT FROM tst_cursor into @col1, @col2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bigtext='<'+ @col1 + '>' +@col2+'</'+ @col1 + '>'
set @textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @textptr @textlen 0 @bigtext
FETCH NEXT FROM tst_cursor into @col1, @col2
END
CLOSE tst_cursor
DEALLOCATE tst_cursor
SET @bigtext='</table>'
set @textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @textptr @textlen 0 @bigtext

select xmlblob from #xmltree

drop table #test
drop table #xmltree

(adrianca@gmail.com) writes:[color=blue]
> --something like below is what i was thinking but is it efficent?[/color]

More to the point: does it work?
[color=blue]
> create table #xmltree( xmlblob text)
> INSERT INTO #xmltree VALUES ('<table>')[/color]

There is not really any way go get the xml from FOR XML into the table.
Well, you can get it to the client, and then INSERT back. Please don't
that. You're wasting bandwidth.
[color=blue]
> --as the client side asp code works out very slow thats why i want to
> do it on the sql server if possible[/color]

For this sort of task, I would expect VBscript to be faster than T-SQL,
since we are only doing string manipulation.

You could write a program in C or C# for the task, but then you would have
to pass the XML string to the C program in some way. If you go by file,
you probably lose on the swings what you gain on the roundabout.

I should add the disclaimer that I have no knowledge about ASP
programming.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

asp 6 and asp.net both took about 10 sec to create the xml client side
from a sql table for a 50k table to xml

Using this method it now takes about 2 sec's by just displaying
resultset.

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
SqlConnection sqlConnection1;
SqlCommand sqlCommand1;

void Page_Load(Object Sender, EventArgs e) {

sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlCommand1 = new System.Data.SqlClient.SqlCommand();
sqlConnection1.ConnectionString = "some connection details";
sqlConnection1.Open();
sqlCommand1.Connection = this.sqlConnection1;
sqlCommand1.CommandText = "sp_getaxml_dataisland";
Response.ContentType = "text/xml";
Response.Write(sqlCommand1.ExecuteScalar().ToStrin g());

}
</script>

For me thats a performance gain worth taking.

(adrianca@gmail.com) writes:[color=blue]
> asp 6 and asp.net both took about 10 sec to create the xml client side
> from a sql table for a 50k table to xml[/color]

Just to check: how did you get the data to the client? You did get
all data into a dataset didn't you?
[color=blue]
> sqlCommand1.CommandText = "sp_getaxml_dataisland";[/color]

sp_ is a prefix that is reserved for system stored procedure, and
SQL Server first looks in master for these. You should not use it
for your own code.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

>Just to check: how did you get the data to the client? You did get[color=blue]
>all data into a dataset didn't you?[/color]
Since the resultset is just one huge SQL Text datatype,
i just wrote it directly to the page, no need for the overhead of
creating a .net dataset object,
and then a javascript dataisland reads it directly.

eg: <xml id="my-dataisland" src="getdataisland.aspx" />
[color=blue][color=green]
>> sqlCommand1.CommandText = "sp_getaxml_dataisland";[/color][/color]

[color=blue]
>sp_ is a prefix that is reserved for system stored procedure, and
>SQL Server first looks in master for these. You should not use it
>for your own code.[/color]
Thanks for the tip i didnt know that.
Do you know if first checks the master table, will that slow down the
request correct / target sp?
I had thought you needed to put "master.dbo.sp_" to access a master sp?

Thanks for your help.

(adrianca@gmail.com) writes:[color=blue]
> Since the resultset is just one huge SQL Text datatype,
> i just wrote it directly to the page, no need for the overhead of
> creating a .net dataset object,
> and then a javascript dataisland reads it directly.[/color]

Javascript is maybe not the fastest. Can you save to a file, and run a
program in a non-interpreted langauge?
[color=blue]
> Do you know if first checks the master table, will that slow down the
> request correct / target sp?
> I had thought you needed to put "master.dbo.sp_" to access a master sp?[/color]

In such case "sp_help" would not work. In fact when you say

somedatabase.dbo.sp_help tbl

what you get information about is somedatabase.dbo.tbl.

Exactly what happens is difficult describe, because it changes every
now and then. But if Microsoft would ship a system procedure called
sp_getaxml_dataisland, you would be in for a nasty surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp