Control XML Output - 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

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)?

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.

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.

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>

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources