正在分析具有递归节点的复杂XML

Question

我遇到了一个非常复杂的XML问题，我花了几天时间尝试代码，并在互联网上搜索解决方案，但收效甚微。

我正在调用一个服务，它以非常复杂和令人困惑的XML格式返回车辆数据。在一些地方，相同命名元素中存在未知数量的相同命名元素。我需要一些来自最低级别的属性信息，也需要一些来自嵌套元素外部的信息。

我需要以下方面的帮助：(1(在价格信息的过程中，可能有零个、一个或十几个"类别"节点。(2( 可以有多个"价格"元素。。。第一个几乎总是零值，然后还有额外的"模糊选项"元素，每个元素下面都有自己的"价格"元素。(3(我需要进入路径的"类别@id"值列表，以降低价格水平(如果有的话(。(4( 我需要"category@id"值的堆栈对"ambigiousoption"敏感。。。第一个"歧义选项"列表中的任何"category@id"值，然后是第二个列表中的任意值。

在我下面的代码中发生的事情是，它在每个"factoryoption"节点中获取第一个(并且只有第一个("price"信息，而不考虑"factoryotion"节点中可能有多个"price’节点，一个在的'ambigioption'元素外，然后在的每个'ambiguoption'元件内有一个额外的'price'节点。

真是一场噩梦。这让我很困惑。帮助

谢谢，Patrick

declare @xml xml = '<vehicledescription bestmodelname="Sierra 1500" beststylename="4WD Crew Cab 143.5&quot; SLE" besttrimname="SLE" bestmakename="GMC" country="US" language="en" modelyear="2014"><factoryoption optionkindid="5" utf="M" fleetonly="false" standard="false" chromecode="FE9" oemcode="FE9"><header id="1156">EMISSIONS</header><description>EMISSIONS, FEDERAL REQUIREMENTS</description><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid><installed cause="OptionCodeBuild" /></price></factoryoption><factoryoption optionkindid="6" utf="E" fleetonly="false" standard="false" chromecode="L83" oemcode="L83"><header id="1160">ENGINE</header><description>ENGINE, 5.3L ECOTEC3 V8 WITH ACTIVE FUEL MANAGEMENT</description><category id="1052"><category id="1213"><price unknown="false" invoicemin="963.6" invoicemax="963.6" msrpmin="1095" msrpmax="1095"><styleid>358200</styleid><installed cause="Engine" /></price></category></category></factoryoption><factoryoption optionkindid="7" utf="T" fleetonly="false" standard="true" chromecode="MYC" oemcode="MYC"><header id="1379">TRANSMISSION</header><description>TRANSMISSION, 6-SPEED AUTOMATIC, ELECTRONICALLY CONTROLLED</description><category id="1104"><category id="1130"><category id="1131" removed="true"><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid><installed cause="OptionCodeBuild" /></price></category></category></category></factoryoption><factoryoption optionkindid="28" utf="F" standard="false" oemcode="H1Y"><header id="1347">SEAT TRIM</header><description>JET BLACK, LEATHER-APPOINTED FRONT SEAT TRIM</description><category id="1077" removed="true"><category id="1078"><category id="1079" removed="true"><category id="1309" removed="true"><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid><installed cause="OptionCodeBuild"><ambiguousoption optionkindid="28" utf="F" fleetonly="false" standard="false" chromecode="H1Y-R" oemcode="H1Y"><header id="1347">SEAT TRIM</header><description>JET BLACK, LEATHER-APPOINTED FRONT SEAT TRIM</description><category id="1074"><category id="1077" removed="true"><category id="1078"><category id="1079" removed="true"><category id="1309" removed="true"><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid></price></category></category></category></category></category></ambiguousoption><ambiguousoption optionkindid="28" utf="F" fleetonly="true" standard="false" chromecode="H1Y-F" oemcode="H1Y"><header id="1347">SEAT TRIM</header><description>JET BLACK, LEATHER-APPOINTED FRONT SEAT TRIM</description><category id="1077" removed="true"><category id="1078"><category id="1079" removed="true"><category id="1156"><category id="1309" removed="true"><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid></price></category></category></category></category></category></ambiguousoption></installed></price></category></category></category></category></factoryoption><factoryoption optionkindid="33" utf="A" fleetonly="false" standard="true" chromecode="IO5" oemcode="IO5"><header id="1301">RADIO</header><description>AUDIO SYSTEM, 8" DIAGONAL COLOR TOUCH SCREEN WITH INTELLILINK, AM/FM/SIRIUSXM/HD</description><category id="1014"><category id="1017" removed="true"><category id="1149"><category id="1150"><category id="1211"><category id="1230"><category id="1299"><price unknown="false" invoicemin="0" invoicemax="0" msrpmin="0" msrpmax="0"><styleid>358200</styleid><installed cause="OptionCodeBuild" /></price></category></category></category></category></category></category></category></factoryoption><factoryoption utf="0" standard="false" oemcode="PDU"><header id="10750">ADDITIONAL EQUIPMENT</header><description>SLE VALUE PACKAGE</description><category id="1009"><category id="1010"><category id="1011"><category id="1034"><category id="1074"><category id="1151"><category id="1204"><category id="1221"><price unknown="false" invoicemin="1425.6" invoicemax="1425.6" msrpmin="1620" msrpmax="1620"><styleid>358200</styleid><installed cause="OptionCodeBuild"><ambiguousoption utf="0" fleetonly="false" standard="false" chromecode="PDU-R" oemcode="PDU"><header id="10750">ADDITIONAL EQUIPMENT</header><description>SLE VALUE PACKAGE</description><category id="1009"><category id="1010"><category id="1011"><category id="1034"><category id="1074"><category id="1151"><category id="1204"><category id="1221"><price unknown="false" invoicemin="1425.6" invoicemax="1425.6" msrpmin="1620" msrpmax="1620"><styleid>358200</styleid></price></category></category></category></category></category></category></category></category></ambiguousoption><ambiguousoption utf="0" fleetonly="true" standard="false" chromecode="PDU-F" oemcode="PDU"><header id="10750">ADDITIONAL EQUIPMENT</header><description>SLE VALUE PACKAGE</description><category id="1009"><category id="1010"><category id="1011"><category id="1034"><category id="1074"><category id="1151"><category id="1204"><category id="1221"><price unknown="false" invoicemin="1425.6" invoicemax="1425.6" msrpmin="1620" msrpmax="1620"><styleid>358200</styleid></price></category></category></category></category></category></category></category></category></ambiguousoption></installed></price></category></category></category></category></category></category></category></category></factoryoption></vehicledescription>'
select @xml
SELECT
--  Nodes.node.value('.', 'varchar(max)') AS category,
  Nodes.node.value('(header/@id)[1]', 'varchar(100)') AS headerid,
  Nodes.node.value('(header/text())[1]', 'varchar(100)') AS headertext,
  Nodes.node.value('description[1]', 'varchar(100)') AS description,
  Nodes.node.value('(.//price/@invoicemin)[1]', 'money') AS invoicemin,
  Nodes.node.value('(.//price/@invoicemax)[1]', 'money') AS invoicemax,
  Nodes.node.value('(.//price/@msrpmin)[1]', 'money') AS msrpmin,
  Nodes.node.value('(.//price/@msrpmax)[1]', 'money') AS msrpmax,
  Nodes.node.value('(.//price/styleid/text())[1]', 'varchar(100)') AS styleid,
  Nodes.node.value('(.//price/installed/@cause)[1]', 'varchar(50)') AS installcause
FROM
  @xml.nodes('//factoryoption') AS Nodes(node);

Answer 1

逐步

FROM
  @xml.nodes('//factoryoption') AS Nodes(node);

这将只为每个<factoryoption>元素产生一行，而与深度无关。你需要把它扇出。

FROM
  @xml.nodes('//factoryoption') AS Nodes1(factoryoption)
CROSS APPLY
  @xml.nodes('category') AS Nodes2(category)

这将为作为<factoryoption> 的子级的每个<category>元素产生一行

FROM
  @xml.nodes('//factoryoption') AS Nodes1(factoryoption)
CROSS APPLY
  factoryoption.nodes('category') AS Nodes2(category)
CROSS APPLY
  category.nodes('price') AS Nodes3(price)

这将为作为<factoryoption> 的子级<category>元素的子级的每个<price>元素产生一行

您需要首先扇出xml。如果您不这样做，而是使用类似(.//price/@invoicemin)[1]的东西，则会要求"在整个子树中不加区分地搜索具有@invoicemin属性的<price>元素，并返回您找到的[1](即第一个(