FOR XML EXPLICIT help - 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 2000 Forums

SQL Server Development (2000)

FOR XML EXPLICIT help

Author

Topic

deekatz
Starting Member

1 Post

Posted - 2006-01-27 : 18:10:38

Getting this far has already took me a lifetime. Can someone help me out? I have this FOR XML EXPLICIT statement.

select 1 as tag,
null as parent,
record_id as [client!1!record_id],
c.system_id as [client!1!system_id],
c.date_added as [client!1!date_added],
c.date_updated as [client!1!date_updated],
c.first_name as [client!1!first_name!element],
c.last_name as [client!1!last_name!element],
c.mi_initial as [client!1!mi_initial],
c.soc_sec_no as [client!1!soc_sec_no!element],
null as [dynamic_content!2],
svpprofrace as [dynamic_content!2!svpprofrace],
svpprofgender as [dynamic_content!2!svpprofgender],
svpprofdob as [dynamic_content!2!svpprofdob]
from tblrawclients c
where clientndx = '45'
for xml explicit

How do I modify the above statement to produce an XML document in the format below:
<clients>
<client record_id='1' system_id ='1' date_added='2000-01-1T12:00:00' date_updated='2000-01-01T12:00:00'>
<first_name>John</first_name>
<last_name>Smith</last_name>
<mi_initial></mi_initial>
<soc_sec_no></soc_sec_no>
<dynamic_content>
<race date_added='2000-01-01T12:00:00' date_effective='2000-01-01T12:00:00'>hispanic</race>
<dob date_added='2000-01-01T12:00:00' date_effective='2000-01-01T12:00:00'>1974-09-01T12:00:00</dob>
<gender date_added='2000-01-01T12:00:00' date_effective='2000-01-01T12:00:00'>male</gender>
</dynamic_content>
</client>
</clients>

date_added and date_effective appears only once in the table but it is being used as attributes by race, gender and dob. Please help. I've been working on this for way too long.

Kristen
Test

22859 Posts

Posted - 2006-01-28 : 02:32:01

You need two "levels" don't you? A header for <client> and then child records for <first_name> etc.

Looks like you are trying to get that with !1! and !2!, but AFAIK you can only do that by UNIONing the !1! stuff with the !2! stuff (second union will need to use
SELECT 2 AS tag,
1 AS Parent,
...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources