December 29, 2021 at 2:28 pm
Hey everyone. I'm a bit new to XML and had an issue with some formatting. I'm nesting XML queries and it creates an additional element <Comp> which I need removed. Here is a sample of what I'm doing.
select
PrintOrderNo as "@PrintOrderID"
,PayGroupID as "@PayGroup"
,(
select
LineNumber as "@DetailNo"
,CompensationDescription as "@Description"
FROM Table1
FOR XML PATH ('Compensation'),type
) as 'Comp'
FROM Table1
FOR XML PATH ('Check'),type
The results look like this:
<Check PrintOrderID="02" PayGroup="ABC Staff">
<Comp>
<Compensation DetailNo="1" Description="BENEFIT"/>
</Comp>
</Check>
but the results should be like the below output with the <Comp> and </Comp> tags removed.
<Check PrintOrderID="02" PayGroup="ABC Staff">
<Compensation DetailNo="1" Description="BENEFIT"/>
</Check>
Any help is appreciated!
December 29, 2021 at 3:14 pm
Just drop the AS 'Comp' from the subquery 🙂
December 29, 2021 at 3:20 pm
Remove the comp alias. Also, that nested select is returning all rows from Table1 for LineNumber and CompensationDescription regardless of which print order no it belongs to
Create table #table1 (PrintOrderNo int,
PayGroupID int,
LineNumber int,
CompensationDescription varchar(10))
insert into #table1 values
(1,1,1,'test1'),
(1,1,2,'test2'),
(2,1,1,'test3'),
(2,1,2,'test4'),
(3,1,1,'test1')
select
PrintOrderNo as "@PrintOrderID"
,PayGroupID as "@PayGroup"
,(
select
LineNumber as "@DetailNo"
,CompensationDescription as "@Description"
FROM #Table1
FOR XML PATH ('Compensation'),type
)
FROM #Table1
FOR XML PATH ('Check'),type
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2021 at 3:32 pm
Okay I thought I tried that and it didn't work but apparently not! Much appreciate!
December 29, 2021 at 4:36 pm
Mike,
Ahh yes I see what you're saying about all the rows being returned. When I included multiple print orders it's including detail for all of print orders and not its respective print order.
Is there a way to "join" the XML subquery so that only specific detail is displayed?
December 29, 2021 at 4:50 pm
Mike,
Ahh yes I see what you're saying about all the rows being returned. When I included multiple print orders it's including detail for all of print orders and not its respective print order.
Is there a way to "join" the XML subquery so that only specific detail is displayed?
Yep just change your subquery to to not requery the table.
select
LineNumber as "@DetailNo"
,CompensationDescription as "@Description"
FOR XML PATH ('Compensation'),type
December 29, 2021 at 5:02 pm
Try joining to it
Create table #table1 (PrintOrderNo int,
PayGroupID int,
LineNumber int,
CompensationDescription varchar(10))
insert into #table1 values
(1,1,1,'test1'),
(1,1,2,'test2'),
(2,1,1,'test3'),
(2,1,2,'test4'),
(3,1,1,'test1')
select
PrintOrderNo as "@PrintOrderID"
,PayGroupID as "@PayGroup"
,(
select
LineNumber as "@DetailNo"
,CompensationDescription as "@Description"
FROM #Table1 t1
where t1.PrintOrderNo = t2.PrintOrderNo
FOR XML PATH ('Compensation'),type
)
FROM #Table1 t2
FOR XML PATH ('Check'),type
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2021 at 5:04 pm
ZZartin,
Thanks for the response! Yes that would apply to the query I posted but in reality I need to have about 5 sub elements for each PrintOrderNo (Compensation, Withholding, Attendance etc.). Also the table that holds the data repeats certain data elements and also has multiple columns for other elements. I'm finding myself doing DISTINCT and CROSS APPLY queries for those other elements.
I could "loop" through each record inserting it into a table to get individual XML records for each PrintOrderNo. Then I could take all of those records and build a final file. I'm still trying to do it as a single select though. 🙂
December 29, 2021 at 7:58 pm
if you provide more relevant data, we can maybe help
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2021 at 1:18 pm
Mike I hadn't seen your reply prior to my previous response but thank you, it was helpful.
I was able to join the sub-queries and that matched everything up perfectly. Everything looks good but I'm wondering if there is a way to clean up the code so it's a bit more readable/manageable since there are multiple sub-queries.
Below is a simplified snippet of what I've got. I know it's not proper code but it represents the outline of how the code is wrapped. In reality it's about 400 lines long. Just trying to make it a bit more readable if possible.
---Main Batch Record-----
select
Batch1 as "@Batch1"
,Batch2 as "@Batch2"
----Check Elements
,(
Select
ColA as "@COLA"
,ColB as "COLB"
---Attendance Elements
,(select Att1... From Table FOR XML PATH)
------------- Direct Deposit Elements ----------
,(select DD1... From Table FOR XML PATH)
---------------- Compensation Elements-------
,(select Comp1... From Table FOR XML PATH)
------------- WithHolding Elements ---------------
,(select With1... From Table FOR XML PATH)
------------ Other Information Elements -------
,(select Oth1... From Table FOR XML PATH)
FROM Table For XML Path
)
FROM Table1
FOR XML PATH ('Batch'),ROOT('PrintFile'),TYPE
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply