如何排除一个以上的节点，所以其中一个排除节点将使用XML DML重新排列其属性? [英] How to exclude more than one node, so one of those exclude will rearrange its attributes using XML DML?
本文介绍了如何排除一个以上的节点，所以其中一个排除节点将使用XML DML重新排列其属性?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
this is a reference from this posted question, as I am looking to rearrange the value of attribute <pos> inside nodes <est_mat>, so the first node of <est_mat> will have <pos>10</pos>, then the second <est_mat> node will have <pos>20</pos> and so on.
I have this query from the help of one of the users from the posted reference mentioned.
So, supposing the following xml structure:
DECLARE @xml XML=
N'<flint>
<app>
<comp>59</comp>
<signal>ORDERBOOK</signal>
<sigref>000000172</sigref>
<date>20170201</date>
<time>114954</time>
<id>SFC</id>
<revision>006</revision>
<data>
<rec>
<rpos>1</rpos>
<revision>006</revision>
<order>
<type>SFC</type>
<orno />
<pono>0</pono>
<seri>GLW</seri>
<item>GEC1H-PCB-00081-01</item>
<sfc_type>BTO</sfc_type>
<revi>46</revi>
<sel_code />
<family />
<qty>300</qty>
<del_qty>0</del_qty>
<rej_qty>0</rej_qty>
<uom>ea</uom>
<clot />
<prio>999</prio>
<wh>J59MF6</wh>
<cdel_date>20170201</cdel_date>
<cdel_time>114954</cdel_time>
<prod_date>20170201</prod_date>
<prod_time>114954</prod_time>
<eff_date>20170201</eff_date>
<eff_time>114954</eff_time>
<target>0</target>
<line />
<status>Planned</status>
<skit_nr />
<pick_stat>N</pick_stat>
<so_orno />
<so_pono>0</so_pono>
<est_mats>
<est_mat>
<pos>10</pos>
<item>GEC1H-BTL-CARDBOARD-BOX</item>
<revi>A</revi>
<opno>0</opno>
<qty>0.004</qty>
<uom>ea</uom>
<wh>J59JW2</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>20</pos>
<item>GEC1H-BTL-FD79-G007-00SA</item>
<revi>A-002</revi>
<opno>0</opno>
<qty>1</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>30</pos>
<item>GEC1H-ESC10-091-SOP-1017KXX</item>
<revi>NR-00</revi>
<opno>0</opno>
<qty>1.1</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>40</pos>
<item>GEC1J-LED-NFSL757DV1-835</item>
<revi>NR-00</revi>
<opno>0</opno>
<qty>9</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>10</pos>
<item>GEC1H-BTL-CARDBOARD-BOX</item>
<revi>A</revi>
<opno>0</opno>
<qty>0.004</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>20</pos>
<item>GEC1H-BTL-FD79-G007-00SA</item>
<revi>A-002</revi>
<opno>0</opno>
<qty>1</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>30</pos>
<item>GEC1H-ESC10-091-SOP-1017KXX</item>
<revi>NR-00</revi>
<opno>0</opno>
<qty>1.1</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>40</pos>
<item>GEC1J-LED-NFSL757DV1-835</item>
<revi>NR-00</revi>
<opno>0</opno>
<qty>9</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>40</pos>
<item>GEC1J-LED-NFSL757DV1-835</item>
<revi>NR-00</revi>
<opno>0</opno>
<qty>9</qty>
<uom>ea</uom>
<wh>J59WP4</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>40</pos>
<item>TESTING2</item>
<revi>46</revi>
<opno>0</opno>
<qty>0.04</qty>
<uom>ea</uom>
<wh>J59JW2</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>10</pos>
<item>TESTING</item>
<revi>46</revi>
<opno>0</opno>
<qty>3</qty>
<uom>ea</uom>
<wh>J59JW2</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>20</pos>
<item>GEC1J-SW3576L11</item>
<revi>46</revi>
<opno>0</opno>
<qty>3</qty>
<uom>ea</uom>
<wh>J59JW2</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
<est_mat>
<pos>30</pos>
<item>GEC1J-SW359EL11</item>
<revi>46</revi>
<opno>0</opno>
<qty>3</qty>
<uom>ea</uom>
<wh>J59JW2</wh>
<backflush>Y</backflush>
<rpl_method />
<point_of_usage />
</est_mat>
</est_mats>
</order>
</rec>
</data>
</app>
</flint>';
和此查询:
SELECT @xml.query(N'/flint/*[local-name()!="est_mats"]') AS [*]
,(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) * 10 AS pos
,em.value(N'item[1]',N'nvarchar(max)') AS item
,em.value(N'qty[1]',N'decimal(16,4)') AS qty
FROM @xml.nodes(N'flint/app/data/rec/order/est_mats/est_mat') AS A(em)
FOR XML PATH('est_mat'),ROOT('est_mats'),TYPE
)
FOR XML PATH(N'flint');
如何通过按上述说明对属性<pos>进行排序来再次生成结构?
How can I generate the structure again by having that attribute <pos> being ordered as explained above ?
此刻查询将抛出相同的xml结构，而不会更改<pos>
At the moment the query throws the same xml structure without changing the value of <pos>
推荐答案
您可以这样尝试:
首先，您的(减少的！)样本数据中的<pos>具有愚蠢的值，以证明解决方案:
First your (reduced!) sample data with silly values in <pos> to demonstrate the solution:
DECLARE @xml XML=
N'<flint>
<app>
<comp>59</comp>
<!--more elements-->
<revision>006</revision>
<data>
<rec>
<rpos>1</rpos>
<revision>006</revision>
<order>
<type>SFC</type>
<!--more elements-->
<so_pono>0</so_pono>
<est_mats>
<est_mat>
<pos>999</pos>
<item>GEC1H-BTL-CARDBOARD-BOX</item>
<!--more elements-->
</est_mat>
<est_mat>
<pos>333</pos>
<item>GEC1H-BTL-FD79-G007-00SA</item>
<!--more elements-->
</est_mat>
</est_mats>
</order>
</rec>
</data>
</app>
</flint>';
-不，我们将重新编号的<est_mats>读入暂存变量
--No we read the re-numbered <est_mats> into a staging variable
DECLARE @est_mats XML=
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) * 10 AS pos
,em.value(N'item[1]',N'nvarchar(max)') AS item
--add more elements here
FROM @xml.nodes(N'flint/app/data/rec/order/est_mats/est_mat') AS A(em)
FOR XML PATH('est_mat'),ROOT('est_mats'),TYPE
);
-现在我们将现有的<est_mats>完全删除
--Now we remove the existing <est_mats> completely
SET @xml.modify(N'delete (flint/app/data/rec/order/est_mats)[1]');
-只需将修改过的零件插入到原处即可
--Just to insert the modified part in the former place
SET @xml.modify(N'insert sql:variable("@est_mats") as last into (flint/app/data/rec/order)[1]');
-这是结果
SELECT @xml;
<flint>
<app>
<comp>59</comp>
<!--more elements-->
<revision>006</revision>
<data>
<rec>
<rpos>1</rpos>
<revision>006</revision>
<order>
<type>SFC</type>
<!--more elements-->
<so_pono>0</so_pono>
<est_mats>
<est_mat>
<pos>10</pos>
<item>GEC1H-BTL-CARDBOARD-BOX</item>
</est_mat>
<est_mat>
<pos>20</pos>
<item>GEC1H-BTL-FD79-G007-00SA</item>
</est_mat>
</est_mats>
</order>
</rec>
</data>
</app>
</flint>
这篇关于如何排除一个以上的节点，所以其中一个排除节点将使用XML DML重新排列其属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
