Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FOR XML EXPLICIT - very slow

Status
Not open for further replies.
benjatado

benjatado

Programmer
May 15, 2005
52
US
I am attempting to create a well formed XML representation of my database using the FOR XML EXPLICIT function. However, after creating a UNION statement for just 4 of tables I need to export, it has practically slowed to a halt!
I am sure some of this has to do with the UNION of the 4 select statements...but that is the only way I have found to do this? If anyone can help me out in a better way of creating a well formed XML representation, I will greatly appreciate it!
I am going to post the statement I have so far below...
Code: 
CREATE PROCEDURE XMLOUTPUT  AS
SELECT DISTINCT 
1 AS Tag,
 NULL AS PARENT,
 dbo.Title.ItemNumber as [Title!1!ItemNumber!element]
, dbo.Title.UPC as [Title!1!UPC!element]
, dbo.Title.Title as [Title!1!Title!element]
, dbo.Title.Duration as [Title!1!Duration!element]
, dbo.Title.ReleaseDD as [Title!1!ReleaseDD!element]
, dbo.Title.ReleaseMM as [Title!1!ReleaseMM!element]
, dbo.Title.ReleaseYY as [Title!1!ReleaseYY!element]
, dbo.Title.NumberofDiscs as [Title!1!NumberofDiscs!element]
, dbo.Title.Label_HK as [Title!1!Lable_HK!element]
, NULL as [TrackNumber!2!Track_Count!element]
, NULL as [TrackNumber!2!CD_Number!element], 
NULL as [dbo.export_track!3!ISRC!element],
NULL as [dbo.export_track!3!Track_Desc!element],
NULL as [dbo.export_track!3!CD!element],
NULL as [dbo.export_track!3!Track!element],
NULL as [dbo.export_track!3!Timing!element],
NULL as [dbo.export_works!4!Work_Desc!element],
NULL as [dbo.export_works!4!Year_Comp!element],
NULL as [dbo.export_works!4!Work_Timing!element],
NULL as [dbo.export_works!4!Recording_Venue!element],
NULL as [dbo.export_works!4!Recording_Dates!element]

FROM dbo.Title
Where dbo.Title.ItemNumber = '8557428'
UNION ALL
SELECT DISTINCT 
		2  as Tag, 
		1 as PARENT, 
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL, 
NULL, 
NULL,
NULL, 
NULL
FROM dbo.Title
INNER JOIN dbo.TrackNumber ON dbo.Title.ItemNumber = dbo.TrackNumber.Item_Code
UNION ALL
SELECT DISTINCT
		3 as TAG,
		2 as PARENT,
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
dbo.export_track.ISRC, 
dbo.export_track.Track_Desc, 
dbo.export_track.CD, 
dbo.export_track.Track, 
dbo.export_track.Timing, 
NULL, 
NULL, 
NULL, 
NULL, 
NULL
FROM dbo.TrackNumber, dbo.Title INNER JOIN dbo.export_track ON dbo.Title.ItemNumber = dbo.export_track.Item_Code
UNION ALL 
SELECT 4 as TAG, 3 as PARENT,
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
dbo.export_track.ISRC, 
dbo.export_track.Track_Desc, 
dbo.export_track.CD, 
dbo.export_track.Track, 
dbo.export_track.Timing, 
dbo.export_works.Work_Desc, 
dbo.export_works.Year_Comp, 
dbo.export_works.Timing, 
dbo.export_works.Recording_Venue, 
dbo.export_works.Recording_Dates

FROM dbo.Title, dbo.TrackNumber, dbo.export_track INNER JOIN  dbo.export_works ON dbo.export_track.WorkID = dbo.export_works.Work_ID

FOR XML EXPLICIT
GO
Thanks!
 
Sort by date Sort by votes
That is saddly the only way I've been able to get it to work right either. How does the query run without the FOR XML EXPLICIT command?

What SP are you running? I know that there were XML changes in SP4. (Some of which seam to suck a**).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Take a look on this:
Code: 
...
FROM dbo.Title, dbo.TrackNumber, dbo.export_track INNER JOIN  dbo.export_works ON dbo.export_track.WorkID = dbo.export_works.Work_ID

FOR XML EXPLICIT
Four tables, only one join. Gottal love Cartesian products thrown onto FOR XML EXPLICIT :p

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
mrdenny - Thanks for the hint, I did check it without the FOR XML and it turned out (thanks to Mr. Vongrunt) that I also needed to add join statements for each select statement...which returned results quicker, just not accurately.
So now, it is not pulling my Where dbo.Title.ItemNumber = ('value') statement?? Nor the JOIN of the second statement?

Where do these Join and Where statements usually go within these queries?

These statements are totally F-ugly!
I would rather just use a For XML Auto (but have not been able to control the hieharchy with it)
 
Upvote 0 Downvote
Can you post you new code.

That's the problem with XML AUTO, it's automatic.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Sure, but I made some changes, and added the where statement to each select statement (which royally blows!!) I sure hope this will be worth it...
Maybe I can slim down the repetition bit, and just include the joins on the last select statement?? Or not??
This is going to get huge, man o' man...

Here's the code... O Yea.. thanks a ton for the support!![2thumbsup]

Code: 
CREATE PROCEDURE EMusic2  AS
SELECT DISTINCT 
1 AS Tag,
 NULL AS PARENT,
 dbo.Title.ItemNumber as [Title!1!ItemNumber!element]
, dbo.Title.UPC as [Title!1!UPC!element]
, dbo.Title.Title as [Title!1!Title!element]
, dbo.Title.Duration as [Title!1!Duration!element]
, dbo.Title.ReleaseDD as [Title!1!ReleaseDD!element]
, dbo.Title.ReleaseMM as [Title!1!ReleaseMM!element]
, dbo.Title.ReleaseYY as [Title!1!ReleaseYY!element]
, dbo.Title.NumberofDiscs as [Title!1!NumberofDiscs!element]
, dbo.Title.Label_HK as [Title!1!Lable_HK!element]
, NULL as [TrackNumber!2!Track_Count!element]
, NULL as [TrackNumber!2!CD_Number!element], 
NULL as [dbo.export_track!3!ISRC!element],
NULL as [dbo.export_track!3!Track_Desc!element],
NULL as [dbo.export_track!3!CD!element],
NULL as [dbo.export_track!3!Track!element],
NULL as [dbo.export_track!3!Timing!element],
NULL as [dbo.export_works!4!Work_Desc!element],
NULL as [dbo.export_works!4!Year_Comp!element],
NULL as [dbo.export_works!4!Work_Timing!element],
NULL as [dbo.export_works!4!Recording_Venue!element],
NULL as [dbo.export_works!4!Recording_Dates!element]

FROM dbo.export_track, dbo.Title
Where dbo.Title.ItemNumber = '8557428'
UNION ALL
SELECT DISTINCT 
		2  as Tag, 
		1 as PARENT, 
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL, 
NULL, 
NULL,
NULL, 
NULL
FROM dbo.Title
INNER JOIN dbo.TrackNumber ON dbo.Title.ItemNumber = dbo.TrackNumber.Item_Code
Where dbo.Title.ItemNumber = '8557428'
UNION ALL
SELECT DISTINCT
		3 as TAG,
		2 as PARENT,
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
dbo.export_track.ISRC, 
dbo.export_track.Track_Desc, 
dbo.export_track.CD, 
dbo.export_track.Track, 
dbo.export_track.Timing, 
NULL, 
NULL, 
NULL, 
NULL, 
NULL
FROM dbo.Title INNER JOIN
dbo.TrackNumber ON dbo.Title.ItemNumber = dbo.TrackNumber.Item_Code INNER JOIN
dbo.export_track ON dbo.TrackNumber.CD = dbo.export_track.CD AND 
dbo.Title.ItemNumber = dbo.export_track.Item_Code
WHERE dbo.Title.ItemNumber = '8557428'
UNION ALL 
SELECT 4 as TAG, 3 as PARENT,
dbo.Title.ItemNumber 
, dbo.Title.UPC 
, dbo.Title.Title 
, dbo.Title.Duration
, dbo.Title.ReleaseDD
, dbo.Title.ReleaseMM
, dbo.Title.ReleaseYY 
, dbo.Title.NumberofDiscs 
, dbo.Title.Label_HK 
, dbo.TrackNumber.Track_Count 
, dbo.TrackNumber.CD,
dbo.export_track.ISRC, 
dbo.export_track.Track_Desc, 
dbo.export_track.CD, 
dbo.export_track.Track, 
dbo.export_track.Timing, 
dbo.export_works.Work_Desc, 
dbo.export_works.Year_Comp, 
dbo.export_works.Timing, 
dbo.export_works.Recording_Venue, 
dbo.export_works.Recording_Dates

FROM dbo.export_works INNER JOIN dbo.Title INNER JOIN
dbo.TrackNumber ON dbo.Title.ItemNumber = dbo.TrackNumber.Item_Code INNER JOIN
dbo.export_track ON dbo.TrackNumber.CD = dbo.export_track.CD AND 
dbo.Title.ItemNumber = dbo.export_track.Item_Code ON 
dbo.export_works.Work_ID = dbo.export_track.WorkID 
Where dbo.Title.ItemNumber = '8557428'
FOR XML EXPLICIT
GO
 
Upvote 0 Downvote
YUCK! I am now getting back results, but it is ordered wrong? Maybe I am missing something here, but I am trying to get achieve a better order to this...like
<Title>
<TrackNumber>
<Track>
<Work>
<Track>
<Work>
<Track>
<Work>
But Instead it does this...

<Title>
<TrackNumber>
<Track>
<Track>
<Track>
<Work>
<Work>
<Work>

And If I add multiple Titles....

<Title>
<Title>
<TrackNumber>
<TrackNumber>
<Track>
<Track>
<Track>
<Track>
<Track>
<Track>
<Work>
<Work>
<Work>
<Work>
<Work>
<Work>

Its almost like it is not getting the joins or something? Just stair steps the hiegarchy and groups each together.

What am I missing here?
 
Upvote 0 Downvote
ok, I'm playing with my XML that I have thats working. It looks like it's all in the order by.

Try putting this in the order by.

Code: 
order by [Title!1!Title!element],
   [TrackNumber!2!CD_Number!element], 
   [dbo.export_track!3!CD!element]

I have a 4 level break down as well. My order by have one element from each level except the bottom level. Mine is a ticketing system and looks like this. (To make life easier I load the data into a temp table before the xml query.)

Code: 
select 1 as TAG,
	null as parent,
	'Ticketing System' as [Source!1!SourceName],
	null as [System!2!System_Name],
	null as [Ticket!3!Ticket_ID],
	null as [Ticket_Info!4!Subject],
	null as [Ticket_Info!4!FontColor],
	null as [Ticket_Info!4!Status_Desc]
union
select 2 as TAG,
	1 as Parent,
	'Ticketing System' as [Source!1!SourceName],
	System_Desc as [System!2!System_Name],
	null as [Ticket!3!Ticket_ID],
	null as [Ticket_Info!4!Subject],
	null as [Ticket_Info!4!FontColor],
	null as [Ticket_Info!4!Status_Desc]
from #MyOpenTickets
union
select 3 as Tag,
	2 as Parent,
	'Ticketing System' as [Source!1!SourceName],
	System_Desc,
	Ticket_ID,
	null,
	null,
	null
from #MyOpenTickets
union
select 4 as Tag,
	3 as Parent,
	'Ticketing System' as [Source!1!SourceName],
	System_Desc,
	Ticket_ID,
	Subject,
	FontColor,
	Status_Desc
from #MyOpenTickets
order by [Source!1!SourceName], [System!2!System_Name], [Ticket!3!Ticket_ID]

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
On my previous comment the FOR XML AUTO should be at the end of the query.

With the order by I get this as my output.

Results said:
<Source SourceName="Ticketing System">
<System System_Name="DBA Queue">
<Ticket Ticket_ID="110053">
<Ticket_Info Subject="Build a Ticketing System" FontColor="#00CC00" Status_Desc="Auto Approved" />
</Ticket>
</System>
<System System_Name="Ticketing Queue">
<Ticket Ticket_ID="110099">
<Ticket_Info Subject="test" FontColor="#00CC00" Status_Desc="Auto Approved" />
</Ticket>
</System>
</Source>
Click to expand...

With out the order by I get this.
Results said:
<Source SourceName="Ticketing System">
<System System_Name="DBA Queue" />
<System System_Name="Ticketing Queue">
<Ticket Ticket_ID="110053" />
<Ticket Ticket_ID="110099">
<Ticket_Info Subject="Build a Ticketing System" FontColor="#00CC00" Status_Desc="Auto Approved" />
<Ticket_Info Subject="test" FontColor="#00CC00" Status_Desc="Auto Approved" />
</Ticket>
</System>
</Source>
Click to expand...

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Thanks for the order by tip!...that really helped alot!

I am now having problems with repeating elements. I would like to display the contents of a view that has several table joins within it, all within one Parent. But it will not nest correctly...it produces xml like...
<my_view>
<table1>apple</table1>
<table2>orange</table2>
</my_view>
<my_view>
<table1>apple</table1>
<table2>peach</table2>
</my_view>
<my_view>
<table1>apple</table1>
<table2>pear</table2>
</my_view>

This makes the 'apple' value repeat 3x. What I need is something like this (if possible):

<my_view>
<table1>apple</table1>
<table2>orange</table2>
<table2>peach</table2>
<table2>pear</table2>
<my_view>

I have set up the query in much the same logic as before...

Select 1 as Tag, NULL as Parent
...
NULL as [my_view!8!Table1!element],
NULL as [my_view!8!Table2!element]
...
UNION ALL
Select 8 as Tag, 7 as Parent
...
my_view.Table1,
my_view.Table2

Hopefully this is enough information for you...my query has reached 8 select statments...so it is quite large, but the logic is the same throughout...even after ordering by ID, it still repeates for each new value in Table2, even when the order by ID of my_view is the same for each.

I hope someone knows what is going askew here???

This is really kickin my a**! [3eyes]

 
Upvote 0 Downvote
You may be putting data in, in the wrong level or something.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
271
grnzbra
grnzbra
ahmedsa2018
  • Locked
  • Question
How to change STRING_AGG to stuff xml to split feature value by pip?
Replies
0
Views
257
ahmedsa2018
ahmedsa2018
ElEye
  • Locked
  • Question
Parse XML via SQL query 1
Replies
1
Views
235
gmmastros
gmmastros
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
697
Volvere
V
ahmedsa2018
  • Locked
  • Question
How to make pivot for features dynamically based on feature name and feature value ?
Replies
1
Views
312
TamarGranor
TamarGranor

Part and Inventory Search

Sponsor

Back
Top