adding an xml root attribute

Question

Post reply

adding an xml root attribute

Adam-424116

Ten Centuries

Points: 1240
More actions
October 2, 2007 at 4:27 pm

#93618

I'm using a "for xml path('object'), root('root')" statement and was looking to add a timestamp attribute to the root tag. So, the final xml would look something like:
<root timestamp="Oct 2, 2007">
<object name="Object 1">
<data field1="Object 1 data" />
</object>
<object name="Object 2">
<data field1="Object 2 data" />
</object>
</root>
currently my query has everything but the root timestamp, and it looks something like this:
SELECT name as '@name', field1 as 'data/@field1'
FROM objects
FOR XML PATH('object'), ROOT('root')
Any ideas/comments would be appreciated!
Thanks,
--Adam

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

It's a bit crude and not very pretty but it does the job!

DECLARE @xml VARCHAR(4000)

SELECT @xml = (SELECT name as '@name', xtype as 'data/@field1'

FROM sysobjects

FOR XML PATH('object'), ROOT('root'))

SELECT REPLACE(@xml, ' ')

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adam-424116 Ten Centuries Points: 1240 More actions · Answer 2

Thanks for the response, but that wasn't quite what I was looking for.

your query:

DECLARE @xml VARCHAR(4000)
SELECT @xml = (SELECT name as '@name', xtype as 'data/@field1' FROM sysobjects FOR XML PATH('object'), ROOT('root'))
SELECT REPLACE(@xml, ' ')

errors at the replace statement saying that replace requires 3 arguments.

I believe what you were trying to accomplish was:

SELECT name as '@name', replace(xtype, ' ','') as 'data/@xtype'

FROM sysobjects

FOR XML PATH('object'), root('root')

which returns:

<root>

</object>

...

</root>

All I'm trying to do is add a timestamp attribute to the root tag:

Thanks again,

--Adam

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

I'm not sure whether you can do it with the new 2005 XML PATH/ROOT features but it's certainly possible using the more complicated (but more powerful) XML EXPLICIT as follows:

SELECT 1 AS Tag,

NULL AS Parent,

GETDATE() AS 'root!1!timestamp',

NULL AS 'object!2!name',

NULL AS 'data!3!field1'

UNION ALL

SELECT 2,

1,

NULL,

name,

NULL

FROM sys.objects

UNION ALL

SELECT 3,

2,

NULL,

name,

type

FROM sys.objects

ORDER BY 'object!2!name', 'data!3!field1'

FOR XML EXPLICIT

Dan
www.firstcs.co.uk

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 4

Apologies, I should preview before posting... The REPLACE statement didn't appear correctly and should have been:

SELECT REPLACE(@xml, '&LT;root&GT;', '&LT;root timestamp="' + CONVERT(VARCHAR(11), GETDATE(), 113) + '"&GT;')

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adam-424116 Ten Centuries Points: 1240 More actions · Answer 5

Using Dan's method of building the root tag manually, I got everything to work with the path() command as well. It's really just a "Nested FOR XML Query" (that's the title of the bol entry) which looks something like this:

SELECT getDate() as '@timestamp',

(select name as '@name', xtype as 'data/@xtype'

from sysobjects

for xml path('object'), type

)

FOR XML PATH('root')

Thanks for the help!

--Adam

timoha-tim SSC Enthusiast Points: 151 More actions · Answer 6

timoha-tim

SSC Enthusiast

Points: 151

April 22, 2009 at 10:57 am

#982351

Thanks for the post, it's very helpful 🙂

califny Old Hand Points: 348 More actions · Answer 7

How to add

with xmlnamespaces( 'http://www.w3.org/TR/html4/' as "h")

to

SELECT getDate() as '@timestamp',

(select name as '@name', xtype as 'data/@xtype'

from sysobjects

for xml path('object'), type

)

FOR XML PATH('root')

so I can get

Adam-424116 Ten Centuries Points: 1240 More actions · Answer 8

Since the WITH clause goes before the query, the example above would be:

with xmlnamespaces('http://ww.w3.org/TR/html4/' as h)

select getDate() as '@timestamp',

( select name as '@name', xtype as 'data/@xtype'

from sysobjects

for xml path('object'),type

)

for xml path('root')

(The BOL, especially http://msdn.microsoft.com/en-us/library/ms177400.aspx, has more, and better, examples)

--Adam