structured XML through queries

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

Transact-SQL (2000)

structured XML through queries

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-07-15 : 07:17:40

hi,
i have a table named master_table the structure for it is:
[Master_key] int ,
[Event_key] [int],
[time_key] [int] ,
[Event_Title] [varchar] (50) ,
[Event_Desc] [varchar] (250) ,
[Event_Location] [varchar] (50) ,
[Phone] [varchar] (50) ,
[Email_id] [varchar] (50) ,
[Event_Duration] [varchar] (50) ,
[Event_type] [int] NOT NULL ,
[Mail_sent] [bit] NOT NULL ,
CONSTRAINT [PK_master_table] PRIMARY KEY CLUSTERED
and second table time_master as
[TIME_KEY] int,
[REMINDER_TIME] varchar(50)

now i need to generate a xml output in the following format:
<?xml version="1.0" encoding="utf-8" ?>
- <Reminders xmlns="http://tempuri.org/EventData.xsd">
- <Reminder Time="2003-03-03 03:34:00">
- <Event id="1">
<EventTitle>Radhika's Birthday</EventTitle>
- <EventDetails>
<EventDesc>Radhika's Birthday after 2 days.Party Time.</EventDesc>
<EventLocation>Holiday Inn</EventLocation>
<Phone>9823091300</Phone>
<MailTo>xyz@hotmail.com</MailTo>
<EventDate>2003-03-05 03:34:00</EventDate>
<EventDuration>2</EventDuration>
<EventType>Birthday</EventType>
</EventDetails>
<MailSent>0</MailSent>
</Event>
</reminder>

Now i am using this query :

select 1 as Tag,
null as Parent,
t.reminder_time as [reminder!1!time],
null as [reminder!1!key],
null as [event_title!2!title!element],
null as [event_title!2!Description!element],
null as [reminder!1!Location],
null as [reminder!1!Phone],
null as [reminder!1!Email],
null as [reminder!1!Duration],
null as [reminder!1!type],
null as [reminder!1!mail_sent!element]
from time_master t,master_table m

union all

select 2,1,
t.reminder_time ,
m.Event_key ,
m.Event_title ,
m.event_desc ,
m.event_location ,
m.phone,
m.Email_id ,
m.event_duration ,
m.event_type ,
m.mail_sent
from master_table m,time_master t
where t.time_key=m.time_key
for xml explicit
----------------------
the out put i am getting is this:
<?xml version="1.0" encoding="utf-8" ?>
- <event>
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="4-5-2002" />
<reminder time="4-5-2002" />
- <reminder time="4-5-2002">
- <event_title>
<title>tdfsgfgesting23</title>
<Description>defdgfdfgscription</Description>
</event_title>
- <event_title>
<title>thhskjd djs kdjl</title>
<Description>jkdljklsa jdjkl s</Description>
</event_title>
- <event_title>
<title>hahahhahah</title>
<Description>ewtretertrtetr</Description>
</event_title>
- <event_title>
<title>testitng</title>
<Description>fsfds</Description>
</event_title>
- <event_title>
<title>this is a title</title>
<Description>description of the event goes here</Description>
</event_title>

the problem is that the xml is not structured properly
please help.
harshal

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-15 : 08:17:37

If I had to guess, I'd say it's because you forgot the JOIN condition in the first part of the UNION:

select 1 as Tag,
null as Parent,
t.reminder_time as [reminder!1!time],
null as [reminder!1!key],
null as [event_title!2!title!element],
null as [event_title!2!Description!element],
null as [reminder!1!Location],
null as [reminder!1!Phone],
null as [reminder!1!Email],
null as [reminder!1!Duration],
null as [reminder!1!type],
null as [reminder!1!mail_sent!element]
from time_master t,master_table m
where t.time_key=m.time_key

union all

...

Why aren't you using ANSI JOIN syntax? It will perform better in SQL Server than WHERE syntax does.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-07-15 : 08:28:25

ok i have tried putting the condition but it still doesn't work.
this is the result i get,here event when i have records in all the <reminder time> nodes they are listed under a single node.
pl. help.
thnx.

<?xml version="1.0" encoding="utf-8" ?>
- <event>
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="4-5-2002" />
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="Jul 12 2002 12:20PM" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="4-5-2002" />
<reminder time="Jul 12 2002 1:20PM" />
<reminder time="Jul 12 2002 12:20PM" />
- <reminder time="4-5-2002">
- <event_title>
<title>tdfsgfgesting23</title>
<Description>defdgfdfgscription</Description>
</event_title>
- <event_title>
<title>thhskjd djs kdjl</title>
<Description>jkdljklsa jdjkl s</Description>
</event_title>
- <event_title>
<title>dhsjkdkl</title>
<Description>jslkdjflks</Description>
</event_title>
- <event_title>
<title>hahahhahah</title>
<Description>ewtretertrtetr</Description>
</event_title>
- <event_title>
<title>hahahhahah</title>
<Description>ewtretertrtetr</Description>
</event_title>
- <event_title>
<title>hahahhahah</title>
<Description>ewtretertrtetr</Description>
</event_title>
- <event_title>
<title>testitng</title>
<Description>fsfds</Description>
</event_title>
- <event_title>
<title>testing weekly</title>
<Description>nothing special</Description>
</event_title>
- <event_title>
<title>this is a title</title>
<Description>description of the event goes here</Description>
</event_title>
- <event_title>
<title>this is a testing event</title>
<Description>this is the description of the title</Description>
</event_title>
</reminder>
</event>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-15 : 08:37:51

I'm not too experienced with FOR XML EXPLICIT, but in perusing Books Online, I found that they included an ORDER BY clause in the statement. I believe you'll have to do the same in order to get the nodes in the right placement, perhaps:

ORDER BY [reminder!1!key], [event_title!2!title!element]

Use that as a starting point and modify it until you get the correct order.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-07-15 : 08:48:20

i have tried it already but it does not give the desired results.
anyways thnxs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources