SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


adding an xml root attribute


adding an xml root attribute

Author
Message
Adam-424116
Adam-424116
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 192
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
Adrian Nichols-360275
Adrian Nichols-360275
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 915
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...
Adam-424116
Adam-424116
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 192
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 name="sysrowsetcolumns">

<data xtype="S" />

</object>

...

</root>



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

<root timestamp="Oct 3 2007">



Thanks again,

--Adam
DanKennedy
DanKennedy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1992 Visits: 389
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
Adrian Nichols-360275
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 915
Apologies, I should preview before posting... The REPLACE statement didn't appear correctly and should have been:



SELECT REPLACE(@xml, '<root>', '<root timestamp="' + CONVERT(VARCHAR(11), GETDATE(), 113) + '">')



Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Adam-424116
Adam-424116
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 192
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
timoha-tim
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 43
Thanks for the post, it's very helpful Smile
califny
califny
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 413
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
Adam-424116
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 192
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search