XML replace two attributes RSS

• • Reply
  

  Tigers21

  Member

  487 Points

  400 Posts

  XML replace two attributes

  Mar 30, 2012 06:28 PM|LINK

  My XML look like this:

  <Files>
      <File FileID="1" Public="true" FileTypeID="5"></File>
      <File FileID="2" Public="false" FileTypeID="5"></File>
  </Files>

  I pass in three parameters to my stored procedure: @fileID, @public and @fileTypeID...How can I now update the two attributes Public and FileTypeID where FileID equals @fileID?

• • Reply
  

  santosh.jagd...

  Star

  7625 Points

  1454 Posts

  Re: XML replace two attributes

  Mar 31, 2012 09:22 AM|LINK

  you should use OPENXML function from SQL server. Create a stored proc and pass this xml file.

  here is one format for OPENXML

  SELECT *
  FROM OPENXML (@index, 'DataSet/Person[@id>1]')
  WITH (Name varchar(100) 'Name' , PhoneNo Varchar(30) 'PhoneNo', id Varchar(10))

  Santosh J
  MCP
  
  • Marked as answer by decker dong - msft on Apr 05, 2012 02:23 AM
• • Reply
  

  kavita_khand...

  Star

  9767 Points

  1930 Posts

  Re: XML replace two attributes

  Apr 02, 2012 09:16 AM|LINK

  
  Run below script in your SQL query anaylzer
  
  --lets say I have Stored procidure as below.
  
  Alter PROCEDURE updateFileTable
  (
       @file NVARCHAR(100),
       @public NVARCHAR(100),
       @fileTypeId NVARCHAR(100)
  )
  AS
  
  -- Step  1 - Creating a Temporary table.
  CREATE TABLE #FileTable
  (
      cFileName XML,
  )
  -- Step  2 - Inserting the test data give by you.
  
  INSERT INTO #FileTable
             ([cFileName])
       VALUES
             ('<Files>'+
                  '<File FileID="1" Public="true" FileTypeID="5"></File>'+
                  '<File FileID="2" Public="false" FileTypeID="5"></File>'+                
              '</Files>')
  
  --*************************
  PRINT 'Before Update'
  SELECT * FROM #FileTable   
  --*************************     
  
  --Step 3 - Updating the data.
  -- here, in just tje below line the last 1 signifies that the while condition has returned true, and only then the update will take place
  
  WHILE EXISTS (SELECT * FROM #FileTable WHERE cFileName.exist('/Files//File/@FileID[.=sql:variable("@file")]') = '1')
      UPDATE #FileTable
      SET    cFileName.modify('replace value of (/Files//File/@FileID[.="1"])[1] with "10"')
  
  --*************************
  PRINT 'After Update'
  SELECT * FROM #FileTable   
  --*************************
  
  DROP table #FileTable
  
  --Now EXECUTE the Stored PROCEDURE USING below STATEMENT.
  
  --EXEC updateFileTable '1','false','9'

  Please mark this post as Answer if it is of help to you!
  I would love to change the world, but they wont give me the source code.
  • Marked as answer by decker dong - msft on Apr 05, 2012 02:23 AM