SQL XQuery 命令递归获取相同类型的后代 [英] SQL XQuery command to recursively get descendants of same type
问题描述
我是 SQL 中 XQuery 的新手，但我了解获取节点和查询值的基础知识.我现在的问题是处理未知深度的层次结构.
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.
我正在编写一个 SQL 查询来返回结果集中的所有行( 以及其他数据).这是我目前所拥有的:
I'm writing a SQL query to return ALL of the lines in the set of results ( 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)')
正如你所看到的，这只会让我达到1 级"，但我需要更深入(想想 Inception！...一个项目中的一个项目......你明白了)
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)
有什么想法吗?
EDIT 添加了部分解决方案.这让我得到子项目(注意//")，而不是子线:
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
这是一个更好的示例:
<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"><BOM></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>
推荐答案
你们很亲近.我认为您需要做的就是使用您评论中的更新版本并将您的第二个 CROSS APPLY 更改为此:
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.
编辑:
好的，我想我现在有了.它要复杂得多，尽管我尝试使用递归 CTE 来完成，但我无法找到直接链接两个递归 CTE 的方法，也无法使用单个递归 CTE 来完成.一个更像大师的人也许能够改善这一点.
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.
我最终得到的是两个表值 UDF，一个用于递归地粉碎所有项目，另一个用于处理每个项目并递归地粉碎所有行:
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.
这篇关于SQL XQuery 命令递归获取相同类型的后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
