Using FOR XML PATH('') twice doesn't work

Epicor ERP 10
1

Hi all,

Is it possible to use the “FOR XLM PATH(”)" twice?

I have PartRev in Subquery1.

I have PartMtl in Subquery2. In Subquery2, I have a Subquery criteria as follows:
PartMtl.PartNum = PartRev.PartNum FOR XML PATH(‘’)

I have calculated field in Subquery2 as follows:
CAST(PartMtl.MtlPartNum AS VARCHAR) + ', ’

I have calculated field in Subquery1 as follows:
REPLACE(REPLACE({SubQuery2} , ‘</Calculated_Materials>’,‘’),‘<Calculated_Materials>’,‘’)

This works fine and gives me results as expected. Only problem is, The combined list of Materials gives me all results for all revisions.

To fix this, I added another subquery criteria in Subquery2 to make sure the results show only the revision numbers selected:
PartMtl.RevisionNum = partrev.RevisionNum FOR XML PATH(‘’)

But I get the following error:
Severity: Error, Table: , Field: , RowID: , Text: Incorrect syntax near the keyword ‘and’.
Incorrect syntax near the keyword ‘FOR’.
Incorrect syntax near the keyword ‘FOR’.
Incorrect syntax near ‘)’.

Here’s the query phrase:
select
[Part].[RefCategory] as [Part_RefCategory],
[Part].[PartNum] as [Part_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[NonStock] as [Part_NonStock],
[PartRev].[Approved] as [PartRev_Approved],
[PartClass].[Description] as [PartClass_Description],
(REPLACE(REPLACE(((select
(CAST(PartMtl.MtlPartNum AS VARCHAR) + ‘, ‘) as [Calculated_Materials]
from Erp.PartMtl as PartMtl
where PartMtl.PartNum = PartRev.PartNum FOR XML PATH(’’) and PartMtl.PartNum = PartMtl.RevisionNum = partrev.RevisionNum FOR XML PATH(‘’))) , ‘</Calculated_Materials>’,‘’),‘<Calculated_Materials>’,‘’)) as [Calculated_Materials_List],
(LEN(Materials_List) - LEN(REPLACE(Materials_List, ‘,’, ‘’))) as [Calculated_count]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
and ( PartRev.Approved = 1 and PartRev.RevisionNum is not null )

left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID

2

What version of SQL are you on? STRING_AGG is way easier to use and was introduced in SQL Server 2017, which is old enough it’s already hitting EOL lol!

4 Likes
3

  1. Follow @jgiese.wci advice and look into string_agg. XML nodes were always a hack, one we thankfully don’t have to deal with anymore.

  2. It’s usually nonsensical to aggregate an aggregate in a single view. If you actually need to do something like that, push the first aggregation into a CTE (my preference for legibility) or a subquery (does the exact same thing).

  3. Use three ` characters to enclose code blocks for easier readability.