How to pass index value from a node in XML DML with SQL

Question

Starting with XML DML in SQL Server, pretty fine at the moment, but I am facing this challenge. I need to iterate through some defined nodes in XML data stored in SQL Server.

Already check this as reference, it gives a clue but still I did not figure it out how to send a SQL variable as an index in XML DML Reference.

Suppose the following XML data:

<materials>
        <est_mat>
          <pos>20</pos>
          <item>BOX</item>
          <qty>0.004</qty>
        </est_mat>
    <est_mat>
          <pos>30</pos>
          <item>xxx-xxx-xxx01</item>
          <qty>1</qty>
        </est_mat>
    <est_mat>
          <pos>40</pos>
          <item>xxx-xxx-xxx02</item>
          <qty>1</qty>
        </est_mat>
</materials>

So what I am looking is to iterate through all number of <est_mat> nodes and replace <pos> attribute starting from 10, then next node will be 20 and so on.

So far I have this :

--starting of code
declare @cnt int = 10
declare @totalchildren varchar(300)
declare @pos int = 1

--returns the number of nodes
SET @totalchildren = (SELECT (XMLData.value('count(/materials/est_mat)', 'int')) 
                      FROM TABLE_XMLFiles 
                      WHERE myref = 173)

WHILE @cnt < @totalchildren
BEGIN
    --PRINT @cnt
    UPDATE TABLE_XMLFiles
    SET XMLData.modify('replace value of (/materials/est_mat/pos[sql:variable("@pos")])[[1]] with sql:variable("@cnt")') 
    WHERE myref = 173

    SET @cnt = @cnt + 1
    SET @pos = @pos + 10
END
--end of code

Error:

XQuery [BinControl_XMLFiles.XMLData.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(pos,xdt:untyped) ?'

Question is: how I can send a SQL variable as an index position like this:

SET XMLData.modify('replace value of (/materials/est_mat/pos/text())[sql:variable("@pos")] 
                    with sql:variable("@cnt")')

as the value which I am replacing it works by sending it this way with sql:variable("@cnt") - already tried it and works but I am still not figuring it out how to send a variable through the index context.

Thanks in advance for your attention.

Answer 1

Why not just ignore the exsting <pos>-element and re-build the XML?

DECLARE @xml XML=
N'<materials>
  <est_mat>
    <pos>20</pos>
    <item>BOX</item>
    <qty>0.004</qty>
  </est_mat>
  <est_mat>
    <pos>30</pos>
    <item>xxx-xxx-xxx01</item>
    <qty>1</qty>
  </est_mat>
  <est_mat>
    <pos>40</pos>
    <item>xxx-xxx-xxx02</item>
    <qty>1</qty>
  </est_mat>
</materials>';

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'/materials/est_mat') AS A(em)
FOR XML PATH('est_mat'),ROOT('materials')

UPDATE Your follow-up question

(Please avoid chameleon questions!)

Your structure might be queried in two combined steps. One query picks out all existing nodes, which are not called <materials> and then adds the query I stated above as a sub-element.

Hint The appropriate date format within XML is ISO8601. Your value 02092017 is culture depending and therefore something you should avoid. Better 2017-02-09 or 2017-02-09T00:00:00 (If it's not the 2nd of September :-) )

DECLARE @xml XML= 
N'<order>
  <orderbook>
    <date> 02092017 </date>
  </orderbook>
  <materials>
    <est_mat>
      <pos>20</pos>
      <item>BOX</item>
      <qty>0.004</qty>
    </est_mat>
    <est_mat>
      <pos>30</pos>
      <item>xxx-xxx-xxx01</item>
      <qty>1</qty>
    </est_mat>
    <est_mat>
      <pos>40</pos>
      <item>xxx-xxx-xxx02</item>
      <qty>1</qty>
    </est_mat>
  </materials>
</order>';

SELECT @xml.query(N'/order/*[local-name()!="materials"]') 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'order/materials/est_mat') AS A(em)
        FOR XML PATH('est_mat'),ROOT('materials'),TYPE
       ) 
FOR XML PATH(N'order');

Attention: The XML's inner order of nodes might be changed...