Problem trying to nest XML using FOR XML PATH

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem trying to nest XML using FOR XML PATH

Author

Topic

emzero
Starting Member

4 Posts

Posted - 2009-01-20 : 14:53:45

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 15:47:20

This should do it

DECLARE	@Sample TABLE
	(
		GalaxyID INT,
		ObjectID VARCHAR(16),
		ObjectType VARCHAR(5),
		ObjectTitle VARCHAR(200)
	)

INSERT	@Sample
SELECT	1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL
SELECT	1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL
SELECT	1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL
SELECT	1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL
SELECT	2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL
SELECT	2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL
SELECT	2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL
SELECT	2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL
SELECT	3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL
SELECT	3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL
SELECT	3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL
SELECT	3, 'T011820001524538', 'Topic', 'Drivers of Change & Growth'

-- Display the XML
SELECT		s.GalaxyID AS [@Id],
		(
			SELECT		x.ObjectID AS [@Id],
					x.ObjectType AS [@Type],
					x.ObjectTitle AS [Title]
			FROM            @Sample AS x
			WHERE		x.GalaxyID = s.GalaxyID
			FOR XML         PATH('Object'),
					TYPE
		)
FROM		(
			SELECT		GalaxyID
			FROM		@Sample
			GROUP BY	GalaxyID
		) AS s
FOR XML		PATH('Galaxy'),
		ROOT('Universe')

E 12°55'05.63"
N 56°04'39.26"

emzero
Starting Member

4 Posts

Posted - 2009-01-20 : 15:54:43

Thanks man!

Works perfect.

Any article where I could learn how to return nested-XML?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 16:23:20

For other interested in this topic, this is the output

<Universe>
  <Galaxy Id="1">
    <Object Id="T022520001611242" Type="Topic">
      <Title>Business Strategy</Title>
    </Object>
    <Object Id="T021320001145243" Type="Topic">
      <Title>Decision Making</Title>
    </Object>
    <Object Id="T8150310322032" Type="Topic">
      <Title>New Growth</Title>
    </Object>
    <Object Id="T97200019493829" Type="Topic">
      <Title>Marketing Strategy</Title>
    </Object>
  </Galaxy>
  <Galaxy Id="2">
    <Object Id="T1210018575047" Type="Topic">
      <Title>Strategic Relationships</Title>
    </Object>
    <Object Id="T1027001655860" Type="Topic">
      <Title>Globalization</Title>
    </Object>
    <Object Id="T95200015582307" Type="Topic">
      <Title>Strategic Sourcing</Title>
    </Object>
    <Object Id="T021120001714561" Type="Topic">
      <Title>Business Processes & Architectures</Title>
    </Object>
  </Galaxy>
  <Galaxy Id="3">
    <Object Id="T011820001527219" Type="Topic">
      <Title>Business Models</Title>
    </Object>
    <Object Id="T022520001622334" Type="Topic">
      <Title>Venture Capital Processes</Title>
    </Object>
    <Object Id="T524200010114538" Type="Topic">
      <Title>Entrepreneurial Thinking</Title>
    </Object>
    <Object Id="T011820001524538" Type="Topic">
      <Title>Drivers of Change & Growth</Title>
    </Object>
  </Galaxy>
</Universe>

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources