How to exclude more than one node, so one of those

问题:

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>';

and this query:

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');

How can I generate the structure again by having that attribute <pos> being ordered as explained above ?

At the moment the query throws the same xml structure without changing the value of <pos>

回答1:

You might try it like this:

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>';

--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
);

--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]');

--This is the result

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>