using a value as an XML node name

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

General SQL Server Forums

New to SQL Server Programming

using a value as an XML node name

Author

Topic

mjkoskinen
Starting Member

5 Posts

Posted - 2011-07-01 : 17:21:44

I have the following SQL query:


SELECT 
   name as "name"
   job as "job"
FROM People FOR XML PATH('Root');

this generates:


<root>
<name>bob</name>
<job>developer</job>
</root>

How could i alter this query to get the following results?


<root>
<bob>developer</bod>
</root>

In otherwords, I want a queried value to be the XML node name. I've spend awhile looking on the internet for the answer to this one but can't seem to stumble upon it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-02 : 07:13:50

W3C's XQuery specification allows you to use computed node names, but unfortunately SQL's implementation of XQuery does not. http://msdn.microsoft.com/en-us/library/ms189928(v=SQL.105).aspx

So there is no direct way I can think of to do this. You might fake it by doing something like the example I am showing below, but it's not a solution, it's a kludge.


create table #tmp (name varchar(255), job varchar(255));
insert into #tmp values ('Bob','Developer'),('Sunita','AnotherDeveloper');

select cast('<'+ name + '>' + job + '</'+ name + '>' as xml)
from #tmp
for xml path(''), root('root')

drop table #tmp;

mjkoskinen
Starting Member

5 Posts

Posted - 2011-07-05 : 12:23:52

quote:
Originally posted by sunitabeck

W3C's XQuery specification allows you to use computed node names, but unfortunately SQL's implementation of XQuery does not. http://msdn.microsoft.com/en-us/library/ms189928(v=SQL.105).aspx

So there is no direct way I can think of to do this. You might fake it by doing something like the example I am showing below, but it's not a solution, it's a kludge.

create table #tmp (name varchar(255), job varchar(255));
insert into #tmp values ('Bob','Developer'),('Sunita','AnotherDeveloper');

select cast('<'+ name + '>' + job + '</'+ name + '>' as xml)
from #tmp
for xml path(''), root('root')

drop table #tmp;

That syntax give the following error

'Msg 9414, Level 16, State 1, Line 3
XML parsing: line 1, character 14, equal expected'

Any ideas on how to fix it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:08:04

Its working for me. I think you're not using it as suggested. Would you mind posting your used query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 13:36:15

What version of SQL Server are you on? If you are on SQL 2005, change the insert statement like this:


insert into #tmp values ('Bob','Developer');
insert into #tmp values ('Sunita','AnotherDeveloper');

If you are on SQL 2000, not sure what the problems may be - I have very little familiarity with SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 13:40:11

quote:
Originally posted by sunitabeck

What version of SQL Server are you on? If you are on SQL 2005, change the insert statement like this:

insert into #tmp values ('Bob','Developer');
insert into #tmp values ('Sunita','AnotherDeveloper');
If you are on SQL 2000, not sure what the problems may be - I have very little familiarity with SQL 2000.

FOR XML PATH is not available on SQL 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 13:54:33

quote:
Originally posted by visakh16

FOR XML PATH is not available on SQL 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oh yes! I knew that!!! (I think... )

mjkoskinen
Starting Member

5 Posts

Posted - 2011-07-05 : 13:58:48

Thanks for the quick replies. I am using SQL Server 2008.

I wrote the following query:

SELECT CAST('<'+Name+'>'+'test'+'</'+Name+'>' AS XML) 
FROM Definitions FOR XML PATH('Root');

This query gives me the error "XML parsing: line 1, character 14, equal expected"

But If I run this query it works fine:

SELECT CAST('<test>'+Name+'</test>' AS XML) 
FROM Definitions FOR XML PATH('Root');

For some reason if the queried value is in the node name it throws an error. Ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:01:45

Even the first one is also working fine for me.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mjkoskinen
Starting Member

5 Posts

Posted - 2011-07-05 : 14:08:51

quote:
Originally posted by visakh16

Even the first one is also working fine for me.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I believe the problem im running into is that my queried value has spaces. If you change sunitabeck original query to insert 'Bob Smith' instead of just 'Bob' you'll see the error I was getting.

Thanks all for your help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 14:42:50

It's complaining because space is not a valid character in an element name. You can read the rather dense naming rules on the W3C recommendations here http://www.w3.org/TR/REC-xml/ or a more readable version here http://www.xml.com/pub/a/2001/07/25/namingparts.html

If you replace spaces with underscores, you should be fine. That said, I am beginning to wonder whether what you really want to accomplish could be achieved without using kludges and dynamic node names. Can you describe what you are trying to do with dynamic node names? Is it for consumption by an external piece of code?

In any case, code that should work:

create table #tmp (name varchar(255), job varchar(255));
insert into #tmp values ('Bob Smith','Developer'),('Sunita','AnotherDeveloper');

select cast('<'+ replace(name,' ','_') + '>' + job + '</'+  replace(name,' ','_') + '>' as xml)
from #tmp
for xml path(''), root('root')

drop table #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources