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
 General SQL Server Forums
 New to SQL Server Programming
 Control XML Output

Author  Topic 

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-11-26 : 18:27:40
I'm trying to set up an XML export. I have the following table and sample queries:


declare @WorkOrders table (WorkOrderId int, WorkOrderDate date)
declare @LaborIssue table (LaborIssueId int, WorkOrderId int, TechnicianId int)
declare @PartIssue table (PartIssueId int, WorkOrderId int, PartDescription varchar(30))

insert into @WorkOrders (WorkOrderId, WorkOrderDate)
values
(1,'2014-10-01')
,(2,'2014-10-01')

insert into @LaborIssue (LaborIssueId, WorkOrderId, TechnicianId)
values
(1, 1, 30)
,(2, 2, 31)
,(3, 2, 32)

insert into @PartIssue (PartIssueId, WorkOrderId, PartDescription)
values
(1, 1, 'Tire')
,(2, 1, 'Brake')
,(3, 2, 'Windshield')

select WO_Header.WorkOrderId
,WO_Header.WorkOrderDate
,LaborIssue.LaborIssueId
,LaborIssue.TechnicianId
,PartIssue.PartIssueId
,PartIssue.PartDescription

from @WorkOrders WO_Header
left join @LaborIssue LaborIssue on LaborIssue.WorkOrderId=WO_Header.WorkOrderId
left join @PartIssue PartIssue on PartIssue.WorkOrderId=WO_Header.WorkOrderId

for xml auto, elements


Using the table structure, is there any way to get output like this...FOR XML Auto is not recognizing that the PartIssue is part of the WorkOrder:


<WO_Header>
<WorkOrderId>1</WorkOrderId>
<WorkOrderDate>2014-10-01</WorkOrderDate>
<LaborIssues>
<LaborIssue>
<LaborIssueId>1</LaborIssueId>
<TechnicianId>30</TechnicianId>
</LaborIssue>
<PartIssues>
<PartIssue>
<PartIssueId>1</PartIssueId>
<PartDescription>Tire</PartDescription>
</PartIssue>
<PartIssue>
<PartIssueId>2</PartIssueId>
<PartDescription>Brake</PartDescription>
</PartIssue>
</WO_Header>
<WO_Header>
<WorkOrderId>2</WorkOrderId>
<WorkOrderDate>2014-10-01</WorkOrderDate>
<LaborIssues>
<LaborIssue>
<LaborIssueId>2</LaborIssueId>
<TechnicianId>31</TechnicianId>
</LaborIssue>
<LaborIssue>
<LaborIssueId>3</LaborIssueId>
<TechnicianId>32</TechnicianId>
</LaborIssue>
<PartIssues>
<PartIssue>
<PartIssueId>3</PartIssueId>
<PartDescription>Windshield</PartDescription>
</PartIssue>
<PartIssue>
<PartIssueId>3</PartIssueId>
<PartDescription>Windshield</PartDescription>
</PartIssue>
</WO_Header>

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 09:33:53
In the output you posted, PartIssues are nested under WO_Header. Can you post your desired output, and some input data to work with (in consumable DDL/DML)?
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-11-27 : 10:31:47
the xml posted is my desired output. i altered the xml response to demostrate what im looking for.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 11:20:16
Ok, So, try putting the nested elements as subqueries in the main SELECT clause.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-11-27 : 13:26:50
Thanks, I tried this...seems a bit sloppy and I hate to use the group by if there's a more elegant way of doing this, but I AM getting the expected output. The XML output is what is correct...if you think the query could be re-written to optimize performance, let me know, thanks!


declare @WorkOrders table (WorkOrderId int, WorkOrderDate date)
declare @LaborIssue table (LaborIssueId int, WorkOrderId int, TechnicianId int)
declare @PartIssue table (PartIssueId int, WorkOrderId int, PartDescription varchar(30))

insert into @WorkOrders (WorkOrderId, WorkOrderDate)
values
(1,'2014-10-01')
,(2,'2014-10-01')

insert into @LaborIssue (LaborIssueId, WorkOrderId, TechnicianId)
values
(1, 1, 30)
,(2, 2, 31)
,(3, 2, 32)

insert into @PartIssue (PartIssueId, WorkOrderId, PartDescription)
values
(1, 1, 'Tire')
,(2, 1, 'Brake')
,(3, 2, 'Windshield')

select WO_Header.WorkOrderId, WO_Header.WorkOrderDate, (

select (
select LaborIssueId, TechnicianId
from @LaborIssue LaborIssue
where LaborIssue.WorkOrderId=LaborIssues.WorkOrderId
group by LaborIssue.LaborIssueId, LaborIssue.TechnicianId
for xml path ('LaborIssue'), elements, type)
from @LaborIssue LaborIssues
where LaborIssues.WorkOrderId=WO_Header.WorkOrderId
group by LaborIssues.WorkOrderId
for xml path ('LaborIssues'), elements, type
)
,
(
select (

select PartIssueId, PartDescription
from @PartIssue PartIssue
where PartIssue.WorkOrderId=PartIssues.WorkOrderId
group by PartIssue.PartIssueId, PartIssue.PartDescription
for xml path ('PartIssue'), elements, type)
from @PartIssue PartIssues
where PartIssues.WorkOrderId=WO_Header.WorkOrderId
group by PartIssues.WorkOrderId
for xml path ('PartIssues'), elements, type
)

from @WorkOrders WO_Header
group by WO_Header.WorkOrderId, WO_Header.WorkOrderDate
for xml path ('WO_Header'), elements


And the results are:

<WO_Header>
<WorkOrderId>1</WorkOrderId>
<WorkOrderDate>2014-10-01</WorkOrderDate>
<LaborIssues>
<LaborIssue>
<LaborIssueId>1</LaborIssueId>
<TechnicianId>30</TechnicianId>
</LaborIssue>
</LaborIssues>
<PartIssues>
<PartIssue>
<PartIssueId>1</PartIssueId>
<PartDescription>Tire</PartDescription>
</PartIssue>
<PartIssue>
<PartIssueId>2</PartIssueId>
<PartDescription>Brake</PartDescription>
</PartIssue>
</PartIssues>
</WO_Header>
<WO_Header>
<WorkOrderId>2</WorkOrderId>
<WorkOrderDate>2014-10-01</WorkOrderDate>
<LaborIssues>
<LaborIssue>
<LaborIssueId>2</LaborIssueId>
<TechnicianId>31</TechnicianId>
</LaborIssue>
<LaborIssue>
<LaborIssueId>3</LaborIssueId>
<TechnicianId>32</TechnicianId>
</LaborIssue>
</LaborIssues>
<PartIssues>
<PartIssue>
<PartIssueId>3</PartIssueId>
<PartDescription>Windshield</PartDescription>
</PartIssue>
</PartIssues>
</WO_Header>

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 13:38:57
It's a long query, to be sure, but using subqueries the way you are is the only practical way to achieve the results you want UNLESS you go for EXPLICIT mode. That's also a lot of work and may not result in a shorter or better-performing query
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-11-27 : 13:56:31
ok, thanks a lot! I'm not all that familiar with XML. I appreciate the advise.
Go to Top of Page
   

- Advertisement -