January 20, 2009 at 1:01 pm
Hi guys, I'm new at this forum =P
I'm having problems with the new FOR XML PATH feature. I'm trying to nest some nodes inside others and all I'm getting is an XML almost exactly as the DataSet I get if I not include the "FOR XML PATH".
Let me explain a bit more.
I have this table (http://emzero.com.ar/xml/table.xls) and I want to return an XML from it. Like this http://emzero.com.ar/xml/XML_I_WANT.xml
But if I do:
select
GalaxyId as '@Id',
ObjId as 'Object/@Id',
ObjType as 'Object/@Type',
ObjTitle as 'Object/Title'
from
Universe
order by
GalaxyId
for xml path('Galaxy'), root('Universe')
I get http://emzero.com.ar/xml/XML_I_GET.xml which is wrong because it's creating an element per row and I want to create just one element per GalaxyId and nest the Objects inside of it. Am I clear?
I don't fully understand how FOR XML PATH and nesting XML works on SQL SERVER 2005. I've read this article (http://msdn.microsoft.com/en-us/library/ms345137.aspx) but I don't get it yet.
Would you be so kind to tell me the correct T-SQL to make that. And also, if it's possible, an article explaing how nesting XML works in SQL Server 2005?
Thank you!
Cheers
January 20, 2009 at 2:07 pm
I get http://emzero.com.ar/xml/XML_I_GET.xml which is wrong because it's creating an element per row and I want to create just one element per GalaxyId and nest the Objects inside of it. Am I clear?
Your XML is formed in a way to have a 1 to many relationship, so you have to query it as such. You are correct in that you need to nest an XML object inside of your query. The way to do that is instead of a column, put
(select col2 as '@field1', col2 as '@field2' from table1 FOR XML path('Object'), TYPE)
The TYPE keyword at the end will automatically convert it into an XML data type. If it is inside another xml data type, it will automatically concatenate itself inside the XML (another way of saying nesting).
Here is an example of nesting
CREATE TABLE #temp
(
col1 int,
col2 varchar(255),
col3 varchar(255),
col4 varchar(255)
)
INSERT INTO #temp select 1, 'test1', 'blah blah', 'blah blah'
INSERT INTO #temp select 1, 'test2', 'halb halb', 'halb halb'
INSERT INTO #temp select 1, 'test3', 'abcdedf', 'hijklmno'
INSERT INTO #temp select 1, 'test4', 'zyxwvuts', 'rqponmlk'
INSERT INTO #temp select 2, 'test1', 'gibberish', 'gibberish'
INSERT INTO #temp select 2, 'test2', 'fillertext', 'fillertext'
INSERT INTO #temp select 2, 'test3', '12345678', '09876543'
INSERT INTO #temp select 2, 'test4', '24682468', '13579135'
select col1 as '@col1',
(select col2 as '@col2', col3 as '@col3', col4 as 'col4', ''
from #temp t2
where t1.col1 = t2.col1
FOR XML PATH('row'), root('inner_xml'), TYPE)
from (select distinct col1 from #temp) t1
for xml path('outer_xml'), root('xml_example')
drop table #temp
January 20, 2009 at 2:34 pm
The way to do this would be to query your Universes table, then a sub-query of the Galaxies table, then a sub-query of the Objects table (or whatever this final table is). Nested sub-queries are pretty straightforward, so it's going to look something like:
select *,
(select *,
(select *
from dbo.Objects
where GalaxyID = Galaxies.GalaxyID
for XML path, type) as Objects
from dbo.Galaxies
where UniverseID = Universes.UniverseID
for XML path, type) as Galaxy
from dbo.Universes
for XML path
Of course, you shouldn't have a table called Objects, but that's what it looks like from the sample data provided. Also shouldn't have a column called ObjID. Those are system names, and it can create problems for you.
Whatever the table names are, the above is a general outline of what the query should look like to produce nested XML.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply