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.

Shnugo · Accepted Answer · 2017-02-10T16:07:00.073

2

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...

edited Feb 10 '17 at 16:07

answered Feb 07 '17 at 22:10

Shnugo

66,100
9
53
114

Thanks Shnugo, this almost answer the question but if I have more nodes above they are not printed selected. For example: DECLARE @xml XML= N' 02092017 20 BOX 0.004 30 xxx-xxx-xxx01 1 40 xxx-xxx-xxx02 1 '; How can this be fixed ? – Antonio Feb 10 '17 at 15:53
one doubt and sorry for the chameleon questions, just new on using xml dml, as you mentioned and I understant he structure, one query excludes the node which contains the elements which I will replace the attributes, is there a way in which you can exclude more than one node, following the example you stated if there is a node which closes until ... BTW, I will vote this as my answer totally, thank you very much. – Antonio Feb 10 '17 at 17:09
@Antonio, sorry, I do not get the last part... If you change this `.query(N'/order/*[local-name()!="materials"]')` to this `.query(N'/order/*[local-name()!="materials" and local-name()!="app"]')` you would get a result **without** the ``element (it won't be taken in the first query and it is not added later. Is this the answer to your question? – Shnugo Feb 10 '17 at 17:15
exactly, how that element would be added or n quantity of elements if they close until that closure of node ? – Antonio Feb 10 '17 at 17:20
@Antonio hey, come on! I asked you to avoid chameleons :-) If I understand this correctly, there's no need to bother about... This query `.query(N'/order/*[local-name()!="materials"]')` will take **all elements (`/*`)** which name is **not** ``. If you need further help I'd ask you to vote and accept this answer and start a new question with an example and the expected output. – Shnugo Feb 10 '17 at 17:43
Ok, I already submit this as the answer and posted a new question please refer [link](http://stackoverflow.com/questions/42168410/how-to-exclude-all-nodes-except-one-that-will-rearrange-its-attributes-using-xml) – Antonio Feb 10 '17 at 20:48
Hi @Antonio, I'll have a look at your new question tomorrow, Just a tiny hint: *I already submit this as the answer* On SO there are two different actions: Voting and Accepting. The votes are set with a click on the triangles and are counted for badges and privileges. The acceptance is the check beside the vote counter and will mark this question as closed. Thx – Shnugo Feb 10 '17 at 23:00
Safe and sound now, voted and accepted as the answer. Thanks to you. – Antonio Feb 10 '17 at 23:14

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

1 Answers1

UPDATE Your follow-up question

Linked