SQL XQuery command to recursively get descendants

2019-06-05 16:53发布

站内问答 / 前端开发
1042 1 5

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>

1条回答
Viruses.
2楼-- · 2019-06-05 17:45

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.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)