Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

adding an xml root attribute Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2007 4:27 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:54 AM
Points: 683, Visits: 189
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
Post #405934
Posted Wednesday, October 3, 2007 10:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, 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...
Post #406297
Posted Wednesday, October 3, 2007 11:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:54 AM
Points: 683, Visits: 189
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
Post #406336
Posted Thursday, October 4, 2007 1:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:21 AM
Points: 1,588, Visits: 387
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
Post #406628
Posted Thursday, October 4, 2007 2:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, 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...
Post #406643
Posted Friday, October 5, 2007 7:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:54 AM
Points: 683, Visits: 189
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
Post #407344
Posted Wednesday, April 22, 2009 10:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 20, 2011 8:04 AM
Points: 71, Visits: 43
Thanks for the post, it's very helpful :)
Post #702544
Posted Saturday, July 25, 2009 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 8:06 AM
Points: 9, Visits: 404
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
<root timestamp="july 25 2009"  xmlns:h="http://www.w3.org/TR/html4/">
<object name="sysrowsetcolumns">
<data xtype="S" />
</object>
</root>

Post #759623
Posted Monday, July 27, 2009 8:30 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:54 AM
Points: 683, Visits: 189
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
Post #760111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse