XML Hierarchy (Table >> XML) - SQL Server Forums

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)

XML Hierarchy (Table >> XML)

Author

Topic

malawneh
Starting Member

24 Posts

Posted - 2007-12-18 : 22:04:38

I am trying to produce the output below. I

<ClientApp>
<ContactAddress1>123 Main St</ContactAddress1>
<ContactAddress2><ContactAddress2>
<ContactCity>San Diego</ContactCity>
<ContactState>CA<ContactState>
<ContactZip>92028</ContactZip>
<Parent>
<FirstName>Michael</FirstName>
<LastName>Smith</LastName>
<Jobs>
<Employer>Mobil</Employer>
<Employer>Steves Towing</Employer>
</Jobs>
</Parent>
<Parent>
<FirstName>Betty</FirstName>
<LastName>Smith</LastName>
<Jobs>
<Employer>Annas Linen</Employer>
<Employer>Macys</Employer>
<Employer>Sears</Employer>
</Jobs>
</Parent>
</ClientApp>

My failed attempt...

SELECT 1 AS TAG
, NULL AS Parent
, f.Address1 AS [ClientApp!1!ContactAddress1!ELEMENT]
, f.Address2 AS [ClientApp!1!ContactAddress2!ELEMENT]
, f.City AS [ClientApp!1!ContactCity!ELEMENT]
, f.StateCode AS [ClientApp!1!ContactState!ELEMENT]
, f.ZipCode AS [ClientApp!1!ContactZip!ELEMENT]
, NULL AS [Parent!2!FirstName!ELEMENT]
, NULL AS [Parent!2!LastName!ELEMENT]
, NULL AS [Parent!2!Job]
FROM ClientPortal.tblFamily f
WHERE ApplicationID = 3

UNION ALL

SELECT 2 --TAG
, 1 --Parent
, NULL --ContactAddress1
, NULL --ContactAddress2
, NULL --ContactCity
, NULL --ContactState
, NULL --ContactZip
, Parent.FirstName --FirstName
, Parent.LastName --LastName
, pse.ParentScheduleEntityID --Job
FROM ClientPortal.tblFamily clientapp
JOIN ClientPortal.tblParent Parent ON Parent.FamilyID = ClientApp.FamilyID
JOIN ClientPortal.tblParentScheduleEntity pse ON pse.ParentID = parent.ParentID
WHERE ClientApp.ApplicationID = 3
FOR XML EXPLICIT

Produces...
<ClientApp>
<ContactAddress1>123456 Blueberry Lane</ContactAddress1>
<ContactAddress2></ContactAddress2>
<ContactCity>Pleasanton</ContactCity>
<ContactState>IA</ContactState>
<ContactZip>50065 </ContactZip>
<Parent Job="973">
<FirstName>Stacy</FirstName>
<LastName>Kennedy</LastName>
</Parent>...

I need help getting to the next Hierarchical level for the Jobs.
any help greatly appreciated.
Thank you all.

Michael Alawneh, DBA

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-18 : 23:11:09

my advice is to abandon this tack immediately. FOR XML EXPLICIT is a pig that should be banished.

since you are using 2005, have a look at the new FOR XML PATH instead:
http://msdn2.microsoft.com/en-us/library/ms345137.aspx#forxml2k5_topic6

elsasoft.org

malawneh
Starting Member

24 Posts

Posted - 2007-12-19 : 17:08:20

Thank you. The reading was very informative and enlightening.

I am new to XML and have not had much exposure to it until now. Even after learning abouth FOR XML PATH I still can not produce the output in the expected hierarchical format.

Why is it so easy to suck in an XML recordset in any hierarchical order but darn near impossible to recreated out of the DB?

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 17:34:48

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93761

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources