SQL XQuery command to recursively get descendants of same type

Question

I'm new to XQuery in SQL but I'm understanding the basics of getting nodes and the values of the queries. My issue now is handling a hierarchy of unknown depth.

The relationship is Contract -> Project -> Lines; and looks something like this:

<ZEstimateContract xmlns="http://schemas.datacontract.org/2004/07/Zeller.Gp" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1">
  <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">QGPET0000000218</Name>
  <_projects>
    <ZEstimateProject z:Id="i10">
      <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project A</Name>
      <_lines>
        <ZEstimateLine z:Id="i41">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">00008813</Name>
          <_lines>
            <ZEstimateLine z:Id="i43">
              <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">002-2075-4  MAIN COLUMN</Name>
              <_lines />
            </ZEstimateLine>
          </_lines>
        </ZEstimateLine>
        <ZEstimateLine z:Id="i44">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">0.080 Aluminum 2ft x 4ft</Name>
          <_lines />
        </ZEstimateLine>
      </_lines>
      <_projects>
        <ZEstimateProject z:Id="i101">          
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project A1</Name>
          <_lines>
            <ZEstimateLine z:Id="i132">              
              <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy" />
              <_lines />
            </ZEstimateLine>
          </_lines>
          <_projects />
        </ZEstimateProject>
      </_projects>
    </ZEstimateProject>
    <ZEstimateProject z:Id="i189">
      <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project B</Name>
      <_lines>
        <ZEstimateLine z:Id="i205">          
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">#8X8SPOOL</Name>
          <_lines />
        </ZEstimateLine>
      </_lines>
      <_projects />
    </ZEstimateProject>
  </_projects>
  <_rebateSources />
</ZEstimateContract>

So, a contract can have any number of projects, which can have any number of lines AND any number of subprojects. The lines can have any number of sublines.

I'm writing a SQL query to return ALL of the lines in the set of results (EDIT: among other data). Here is what I have so far:

-- TEST DATA
DECLARE @QuoteDate DATETIME = '12/12/2011'
DECLARE @QuoteNumber VARCHAR(15) = 'QGPET0000000218'
DECLARE @RevLevel VARCHAR(50) = '0'

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Zeller.Gp' AS ZC, 
    'http://schemas.datacontract.org/2004/07/Zynergy' AS ZYN,
    'http://schemas.microsoft.com/2003/10/Serialization/' AS Z)

SELECT CM.Contract, CM.RevisionLevel, CM.CustomerGpId,
    p.value('(./ZYN:Name)[1]', 'varchar(50)') as ProjectName,
    l.value('(./ZYN:Name)[1]', 'varchar(50)') as ItemNumber
FROM dbo.tblContractMaster AS CM
CROSS APPLY CM.FullContract.nodes('/ZC:ZEstimateContract/ZC:_projects/ZC:ZEstimateProject') as Proj(p)
CROSS APPLY Proj.p.nodes('./ZC:_lines/ZC:ZEstimateLine') as Line(l) 
WHERE CM.[Contract] = @QuoteNumber AND CM.RevisionLevel = @RevLevel
-- Order by the default "ID" that gets assigned to the XML element.  
-- This is the same order that the object is in when in a collection in GPET
ORDER BY p.value('(./@Z:Id)[1]', 'varchar(50)'), l.value('(./@Z:Id)[1]', 'varchar(50)')

As you could see, this will only get me "level 1", but I need to go deeper (think Inception!...a project within a project within a...you get the point)

Any ideas?

EDIT Added a partial solution. This gets me sub-projects (notice the "//"), just not sub-lines:

CROSS APPLY CM.FullContract.nodes('/ZC:ZEstimateContract//ZC:_projects/ZC:ZEstimateProject') as Proj(p) 
CROSS APPLY Proj.p.nodes('./ZC:_lines/ZC:ZEstimateLine') as Line(l) LEFT OUTER JOIN

EDIT: Here is a better sample:

<ZEstimateContract xmlns="http://schemas.datacontract.org/2004/07/Zeller.Gp" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1">
  <_key xmlns="http://schemas.datacontract.org/2004/07/Zynergy">ZynergyDefault</_key>
  <_dataStoreGuid xmlns="http://schemas.datacontract.org/2004/07/Zynergy">88381fa0-5901-4513-9ccb-b2f576341db1</_dataStoreGuid>
  <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">QGPET0000000218</Name>
  <_projects>
    <ZEstimateProject z:Id="i10">
      <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project A</Name>
      <Parent i:nil="true" />
      <_lines>
        <ZEstimateLine z:Id="i41">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">00008813</Name>
          <Description>Epic circular connector w/ 12 inserts.</Description>
          <Parent i:nil="true" />
          <_lines>
            <ZEstimateLine z:Id="i43">
              <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">002-2075-4  MAIN COLUMN</Name>
              <Description>CUSTOM JBOX, PER DWG REV -, PUNCHED, TAPPED, CUT OUTS, PBT4-70003 TEXTURE BLACK INSIDE AND OUTSIDE</Description>
              <Parent z:Id="i44">
                <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">00008813</Name>
                <Description>Epic circular connector w/ 12 inserts.</Description>
                <Parent i:nil="true" />
                <_lines>
                  <ZEstimateLine z:Id="i46">
                    <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">002-2075-4  MAIN COLUMN</Name>
                    <Description>CUSTOM JBOX, PER DWG REV -, PUNCHED, TAPPED, CUT OUTS, PBT4-70003 TEXTURE BLACK INSIDE AND OUTSIDE</Description>
                    <Parent z:Ref="i44" />
                    <_lines />
                  </ZEstimateLine>
                </_lines>
              </Parent>
              <_lines />
            </ZEstimateLine>
          </_lines>
        </ZEstimateLine>
        <ZEstimateLine z:Id="i47">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">AG30</Name>
          <Description>480V 30A CLASS G FUSE</Description>
          <Parent i:nil="true" />
          <_lines />
        </ZEstimateLine>
      </_lines>
      <_projects>
        <ZEstimateProject z:Id="i105">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project A1</Name>
          <_lines>
            <ZEstimateLine z:Id="i136">
              <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">0026153</Name>
              <Description>Olflex 810 16awg 7cond</Description>
              <_lines />
            </ZEstimateLine>
          </_lines>
          <_projects>
            <ZEstimateProject z:Id="i193">
              <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">&lt;BOM&gt;</Name>
              <Parent z:Ref="i105" />
              <_lines>
                <ZEstimateLine z:Id="i224">
                  <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">PROSINE 1000</Name>
                  <Parent i:nil="true" />
                  <_lines />
                </ZEstimateLine>
              </_lines>
              <_projects />
              <_savedBudgets />
            </ZEstimateProject>
          </_projects>
          <_savedBudgets />
        </ZEstimateProject>
      </_projects>
    </ZEstimateProject>
    <ZEstimateProject z:Id="i281">
      <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">Project B</Name>
      <Parent i:nil="true" />
      <_lines>
        <ZEstimateLine z:Id="i297">
          <Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy">#8X8SPOOL</Name>
          <Description>1-8"x14.05" flg x flg. DIP, Black</Description>
          <Parent i:nil="true" />
          <_lines />
        </ZEstimateLine>
      </_lines>
      <_projects />
    </ZEstimateProject>
  </_projects>
</ZEstimateContract>

I still can't figure out a way to get the lines under the ZEstimateLine tag. I'm getting sub-projects, just not sub-lines. — Thelonias, Dec 13 '11 at 13:40

mwigdahl · Accepted Answer · 2011-12-13T17:43:38.040

You are very close. I think all you need to do is use the updated version from your comment and change your second CROSS APPLY to this:

CROSS APPLY Proj.p.nodes('.//ZC:_lines/ZC:ZEstimateLine') as Line(l)

This uses the same extra slash you discovered earlier to recurse down and find all the lines.

You'll want to test this against your data, but against your sample it seems to work correctly.

EDIT:

OK, I think I have it now. It's substantially more complicated and although I tried to do it with recursive CTEs, I couldn't figure out a way to directly chain two recursive CTEs nor could I do it with a single one. A more guru-ish person might be able to improve this.

What I ended up with was two table-valued UDFs, one to shred out all your projects recursively, and the other to take each one of those projects and recursively shred out all the lines:

CREATE FUNCTION fn_explode_projects (@xdata xml)
RETURNS TABLE
AS
RETURN
WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Zeller.Gp' AS ZC, 
    'http://schemas.datacontract.org/2004/07/Zynergy' AS ZYN,
    'http://schemas.microsoft.com/2003/10/Serialization/' AS Z),
ProjList (ProjectName, ChildProjects, ChildLines)
AS
(
    SELECT CAST(NULL as varchar(50)) AS ProjectName,
        @xdata.query('/ZC:ZEstimateContract/ZC:_projects') AS ChildProjects,
        CAST(NULL AS xml) AS ChildLines

    UNION ALL

    SELECT CP.ChildProject.value('(./ZYN:Name)[1]', 'varchar(50)') AS ProjectName,
        CP.ChildProject.query('./ZC:_projects') AS ChildProjects,
        CP.ChildProject.query('./ZC:_lines') AS ChildLines

    FROM ProjList
        CROSS APPLY ProjList.ChildProjects.nodes('/ZC:_projects/ZC:ZEstimateProject') AS CP(ChildProject)
)
SELECT ProjectName, ChildProjects, ChildLines
FROM ProjList

GO


CREATE FUNCTION fn_explode_lines (@xdata xml)
RETURNS TABLE
AS
RETURN
WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Zeller.Gp' AS ZC, 
    'http://schemas.datacontract.org/2004/07/Zynergy' AS ZYN,
    'http://schemas.microsoft.com/2003/10/Serialization/' AS Z),
ItemList (ItemNumber, ChildLines)
AS
(
    SELECT CAST(NULL as varchar(50)) AS ItemNumber,
        @xdata.query('/ZC:_lines') AS ChildLines

    UNION ALL

    SELECT CL.ChildLine.value('(./ZYN:Name)[1]', 'varchar(50)') AS ItemNumber,
        CL.ChildLine.query('./ZC:_lines') AS ChildLines

    FROM ItemList
        CROSS APPLY ItemList.ChildLines.nodes('/ZC:_lines/ZC:ZEstimateLine') AS CL(ChildLine)
)
SELECT ItemNumber, ChildLines
FROM ItemList
GO

You can then use these to perform your query:

SELECT CM.Contract, CM.RevisionLevel, CM.CustomerGpId,
    Proj.ProjectName,
    Line.ItemNumber
FROM dbo.tblContractMaster AS CM
    CROSS APPLY dbo.fn_explode_projects(CM.FullContract) Proj
    CROSS APPLY dbo.fn_explode_lines(Proj.ChildLines) Line
WHERE Proj.ProjectName IS NOT NULL AND Line.ItemNumber IS NOT NULL

This assumes that the contract doesn't contain lines directly -- all lines must be contained by a project or another line.

I tried that earlier, but I end up with too many results...some duplicate rows and some rows that show the incorrect parent project. I'm assuming this has something to do with the way CROSS APPLY works? — Thelonias, Dec 13 '11 at 14:46
Probably. Do you have a more extensive sample data set that demonstrates the problem? — mwigdahl, Dec 13 '11 at 14:49
Yeah...give me one minute to clean it up and remove some sensitive data. — Thelonias, Dec 13 '11 at 14:51
I added a new sample. I couldn't submit the full sample because the XML file was too big. I had to take out a bunch of nodes, not pertinent to the problem that's showing up. I think I see the issue though, and that lies in the fact that a Line has a Parent node (which is the parent line, and ultimately has a "_lines" node which I guess is being picked up). I have no clue how to avoid that because I really don't think I'll be able to remove that from my business object — Thelonias, Dec 13 '11 at 15:10
Awesome, thanks for your help. I was trying to messing around with trying to delete every "Parent" node from the whole XML doc because for my stored procedure, I don't need it. For some reason, this wasn't working at all...the document was never modified. I was doing it like: SET data.modify('delete (/ZEstimateContract//_projects/ZEstimateProject/Parent)[1]') but it wasn't working — Thelonias, Dec 13 '11 at 16:43
This is it! I was looking at CTEs but couldn't grasp the concept too well just yet. I just modified the project function to return more data than just the Name, so I understand mostly how this is working. Thanks so much for your help. Would you mind explaining what the point of the first SELECT on the Project function? Is that to populate "ProjList" with it's first set of "child projects"? — Thelonias, Dec 13 '11 at 18:40
All recursive CTEs have an "anchor" section and a "recursive" section. The "anchor" section is the fixed, top-level data you start with, and the "recursive" section performs operations against it to generate a new data set to work on, etc. More details here: http://msdn.microsoft.com/en-us/library/ms186243.aspx. In this case it's nice because it gets you past the initial "contract" tag and into the purely self-similar XML structure. — mwigdahl, Dec 13 '11 at 18:54

SQL XQuery command to recursively get descendants of same type

1 Answers1

Linked