0

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>

Community
  • 1
  • 1
Antonio
  • 87
  • 8
  • Hi Antonio: For your next question: Try to reduce sample data to the needed minimum. We need none, one or many, so 2 or three `` were sufficient. All elements on the same level might be reduced to one or two of them. If it works for two, it should work for many... And please always poste the expected output according to the sample's input. – Shnugo Feb 11 '17 at 21:25

1 Answers1

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>
Shnugo
  • 66,100
  • 9
  • 53
  • 114