loop through xml nodes in sql

Question

Hi,

 

I would like to loop through myXml nodes and modify them in sql.

i have got something like this:

 

<Root>

  <ValueHolder>

     <Value>3.00</Value>

     <IsNoteDirty>false</IsNoteDirty>

     <Timestamp>

        <StampType>Month</StampType>

        <Stamp>3</Stamp>

        <Year>2007</Year>

     </Timestamp>

     </ValueHolder>

     <ValueHolder>

        <Value xsi:nil="true" />

        <IsNoteDirty>false</IsNoteDirty>

        <Timestamp>

           <StampType>Month</StampType>

           <Stamp>4</Stamp>

           <Year>2007</Year>

        </Timestamp>

     </ValueHolder>

     <ValueHolder>

        <Value>2.00</Value>

        <IsNoteDirty>false</IsNoteDirty>

        <Timestamp>

           <StampType>Month</StampType>

           <Stamp>5</Stamp>

           <Year>2007</Year>

        </Timestamp>

     </ValueHolder>

     <ValueHolder>

        <Value>5.00</Value>

        <IsNoteDirty>false</IsNoteDirty>

       <Timestamp>

          <StampType>Month</StampType>

          <Stamp>6</Stamp>

          <Year>2007</Year>

       </Timestamp>

      </ValueHolder>

</Root>

 

And i want to loop over all the "Value" nodes and modify their value.

How can I do it?

Answer 1

This should get you started.  The following query will list out the value.  From there, it will depend on what you want updated before we update it.  Hope this helps.
Tim

declare @xml xml
set @xml = '
<Root>
  <ValueHolder>
     <Value>3.00</Value>
     <IsNoteDirty>false</IsNoteDirty>
     <Timestamp>
        <StampType>Month</StampType>
        <Stamp>3</Stamp>
        <Year>2007</Year>
     </Timestamp>
     </ValueHolder>
  <ValueHolder>
     <Value>23.00</Value>
     <IsNoteDirty>false</IsNoteDirty>
     <Timestamp>
        <StampType>Month</StampType>
        <Stamp>3</Stamp>
        <Year>2007</Year>
     </Timestamp>
     </ValueHolder>
</Root>'

select Tab.Col.value('(Value)[1]','MONEY')
from
    @xml.nodes('/Root/ValueHolder') Tab(Col)

Answer 2

The following code can be used to map the whole xml structure and values stored on a document from which we only know the name of its root.

When I tested it it worked for just a few runs, afterwards it kind of sucked too much of the sql resources and unrelated transactions started to suffer.

I tried looking for ways to make it work with the nodes() function but only the old `OPENXML` did what I needed done.

I then inserted it on a table variables and manipulated it.

For this specific question, Dynamic sql could be generated to verify if the necessary tables exists or not and after creating them if needed the proceed with value insertion.


   DECLARE @idoc INT
    DECLARE @param_XML XML =
    '<root>
        <element1>hello
            <element2>1</element2>
            <element3 id="3">goodbye</element3>
        </element1>
    </root>'
    
    EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @param_XML
    
    
    --SELECT id, parentid, nodetype,localname, prev, [text]
    SELECT *
    FROM OPENXML (@idoc, '/root', 2)
    
    -- remeber to close document to avoid memory leak
    EXEC sys.sp_xml_removedocument @idoc

Answer 3

Hope the below query will help you

declare @x xml
set @x='<root>
<item>
<test>23</test>
</item>
</root>'
declare @value varchar(10)
set @value = 'val1'
set @x.modify('replace value of (/root/item/test/text())[1] with sql:variable("@value")')

select @x

Answer 4

Check following blog posts on updating values of nodes in an XML document:

UPDATE & DELETE: http://sqlwithmanoj.wordpress.com/2011/10/31/using-dml-with-xml-update-delete/

INSERT: http://sqlwithmanoj.wordpress.com/2011/10/17/using-dml-with-xml-insert/

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 5

why doesn't count and pointer work with nil?

use tempdb;
go
declare @x xml;

set @x = N'
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema">
  <ValueHolder>
     <Value>3.00</Value>
     <IsNoteDirty>false</IsNoteDirty>
     <Timestamp>
        <StampType>Month</StampType>
        <Stamp>3</Stamp>
        <Year>2007</Year>
     </Timestamp>
     </ValueHolder>
     <ValueHolder>
        <Value xsi:nil="true" />
        <IsNoteDirty>false</IsNoteDirty>
        <Timestamp>
           <StampType>Month</StampType>
           <Stamp>4</Stamp>
           <Year>2007</Year>
        </Timestamp>
     </ValueHolder>
     <ValueHolder>
        <Value>2.00</Value>
        <IsNoteDirty>false</IsNoteDirty>
        <Timestamp>
           <StampType>Month</StampType>
           <Stamp>5</Stamp>
           <Year>2007</Year>
        </Timestamp>
     </ValueHolder>
     <ValueHolder>
        <Value>5.00</Value>
        <IsNoteDirty>false</IsNoteDirty>
       <Timestamp>
          <StampType>Month</StampType>
          <Stamp>6</Stamp>
          <Year>2007</Year>
       </Timestamp>
      </ValueHolder>
</Root>
';

declare @i int = @x.value('count(/Root/ValueHolder/Value/text())','decimal(3,2)') --where @x.exist('/Root/ValueHolder/Value') = 1
declare @j int = 0
select @i 
WHILE @j <= @i 
BEGIN
	set @x.modify('
	replace value of 
		(/Root/ValueHolder[sql:variable("@j")]/Value/text())[1] 
	with
		sql:variable("@j")
	');

	SET @j += 1
END

select @x as x;
go

Answer 6

Hi,

OPENXML is a must use here and for tranversing data in XML try using CURSORS, alteast i am doing this.

Answer 7

Hello,

The below links might help you.

http://stackoverflow.com/questions/5006230/how-do-i-iterate-through-the-nodes-of-a-xml-field-in-t-sql

http://www.sqlservercentral.com/Forums/Topic474491-356-5.aspx#bm1210429

Answer 8

check this link

http://www.sqlservercentral.com/articles/XML/62290/

Answer 9

Could you check it might help you out.

http://queryequivalent.blogspot.in/2012/03/loop-through-xml-nodes-in-sql.html