Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

create xml

Posted on 2008-11-03
7
155 Views
Last Modified: 2012-05-05
I am trying to create a stored procedure that returns the output in XML.  I have attached the sample of what I have below, which works, but it is going to be a big hit on performance once there are a more records in the database.
How can I re-write this to make it faster?
SELECT QuestionnaireSections.SectionId,ISNULL(sectiontitle,'') AS sectiontitle,ISNULL(SectionDescription,'') AS SectionDescription,ISNULL(NumberingSequence,'') AS NumberingSequence,ISNULL(DisplaySettings,'') AS DisplaySettings,ISNULL(RepeatHeaders,'') AS RepeatHeaders,ISNULL(Startdate,'') AS Startdate,ISNULL(enddate,'') AS enddate,
	(SELECT SubSections.SectionId,ISNULL(sectiontitle,'') AS sectiontitle,ISNULL(SectionDescription,'') AS SectionDescription,ISNULL(NumberingSequence,'') AS NumberingSequence,ISNULL(DisplaySettings,'') AS DisplaySettings,ISNULL(RepeatHeaders,'') AS RepeatHeaders,ISNULL(Startdate,'') AS Startdate,ISNULL(enddate,'') AS enddate,
		(SELECT Questions.QuestionId AS QuestionId,ResponseOptions AS OptionId,ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
			(SELECT OptionId AS OptionId,OptionText As OptionText
			FROM IMQ.tbl_Q_OptionChoices OptionChoices
			WHERE OptionChoices.QuestionId = Questions.QuestionId FOR XML AUTO, TYPE
			),
			(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
				(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
				FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails 
				WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
				)
			FROM imq.tbl_Q_WorkFlow WorkFlow 
			WHERE WorkFlow.QuestionIdCurrent = Questions.QuestionId FOR XML AUTO, TYPE
			),
			(SELECT SubQuestions.QuestionId AS QuestionId,ResponseOptions AS OptionId,ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
				(SELECT OptionId AS OptionId,OptionText As OptionText
				FROM IMQ.tbl_Q_OptionChoices OptionChoices
				WHERE OptionChoices.QuestionId = SubQuestions.QuestionId FOR XML AUTO, TYPE
				),
				(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
					(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
					FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails 
					WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
					)
				FROM imq.tbl_Q_WorkFlow WorkFlow 
				WHERE WorkFlow.QuestionIdCurrent = SubQuestions.QuestionId FOR XML AUTO, TYPE
				),
				(SELECT SubChildQuestions.QuestionId AS QuestionId,ResponseOptions AS OptionId, ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
					(SELECT OptionId AS OptionId,OptionText As OptionText
					FROM IMQ.tbl_Q_OptionChoices OptionChoices
					WHERE OptionChoices.QuestionId = SubChildQuestions.QuestionId FOR XML AUTO, TYPE
					),
					(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
						(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
						FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails 
						WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
						)
					FROM imq.tbl_Q_WorkFlow WorkFlow 
					WHERE WorkFlow.QuestionIdCurrent = SubChildQuestions.QuestionId FOR XML AUTO, TYPE
					)
				FROM IMQ.tbl_Q_Question SubChildQuestions
				INNER JOIN IMQ.tbl_Map_ParentToSubQuestions sq_scq ON SubChildQuestions.questionId = sq_scq.SubquestionId AND SubQuestions.QuestionId = sq_scq.ParentquestionId
				WHERE SubQuestions.questionId = sq_scq.ParentquestionId FOR XML AUTO,TYPE
				)
			FROM IMQ.tbl_Q_Question SubQuestions 
			INNER JOIN IMQ.tbl_Map_ParentToSubQuestions q_sq ON SubQuestions.questionId = q_sq.SubquestionId
			WHERE Questions.questionId = q_sq.ParentquestionId FOR XML AUTO,TYPE
			) 
		FROM IMQ.tbl_Q_Question Questions 
		INNER JOIN IMQ.tbl_Map_QuestionToSection q_s ON Questions.questionId = q_s.questionId
		WHERE q_s.sectionId = SubSections.SectionId OR q_s.sectionId = QuestionnaireSections.SectionId FOR XML AUTO,TYPE
		)
	FROM IMQ.tbl_QuestionnaireSections SubSections
	LEFT JOIN IMQ.tbl_Map_SectionToSubSection s_s ON s_s.SubsectionId = SubSections.SectionId
	WHERE s_s.ParentSectionId = QuestionnaireSections.SectionId 
		FOR XML AUTO, TYPE
	)
FROM IMQ.tbl_QuestionnaireSections QuestionnaireSections 
WHERE QuestionnaireSections.VersionId = @versionid
FOR XML AUTO, TYPE

Open in new window

0
Comment
Question by:PsychoDazey
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22868161
Generating XML with SQL is NEVER going to be extremely efficient.  But I do notice that the only criteria you have is to join your subqueries out to the parent item.  Do you really need to generate your entire structure every time?
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22868191
Yes, the xml structure is going to be saved in the database so it can later be extracted and modified.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22869433
But you're not saving the XML in the database, you're building the XML from the data in the database.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Author Comment

by:PsychoDazey
ID: 22869498
Right, and then using that data to run a report.  My problem isn't generating the XML, its the query that I'm using isn't well-written and it is starting to time out.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22869523
You don't have do to it with all of those nested queries.  You can do it with a series of joins.  This will allow the query to be run with or without it going to XML.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22869574
Sounds good, can you please give me an example?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22869770
Here you go.
create table #ParentStuff
     (StuffId       int identity primary key clustered
     ,StuffName     nvarchar(32) not null
     ,StuffData     nvarchar(255) null
     )
insert into #parentStuff (stuffName,StuffData)
          select 'Test',null
union all select 'Test2','Description'
 
create table #ChildStuff
     (StuffId       int identity primary key clustered
     ,ParentStuffid int
     ,StuffName     nvarchar(32) not null
     ,StuffData     nvarchar(255) null
     )
insert into #ChildStuff (parentstuffid,stuffName,StuffData)
          select 1,'Child Test','Child Test Data'
union all select 1,'Test2','Child Test 2 Description'
union all select 2,'Child Test','Child Test Data'
union all select 2,'Test2','Child Test 2 Description'
go
select parent.stuffid,parent.stuffname,parent.stuffdata, child.stuffid,child.stuffname,child.stuffdata
from #ParentStuff parent
  join #childstuff child
    on parent.stuffid = child.parentstuffid
go
select parent.stuffid,parent.stuffname,parent.stuffdata, child.stuffid,child.stuffname,child.stuffdata
from #ParentStuff parent
  join #childstuff child
    on parent.stuffid = child.parentstuffid
for xml auto,type,root('Stuff')
go
drop table #ParentStuff
drop table #ChildStuff

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
Need help with the proper SQL syntax when querying a linked Server 1 54
Change variables in SQL table 6 94
execute a MS SQL script as a schedule SQL job 72 138
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question