Update multiple rows in one column with XML in MS SQL

Question

I'm trying to update added column "XML_File" for all(~50 000) rows with each rows XML file, so solution with CASE, WHEN, THEN is not reasonable. For test purposes I'm using AdventureWorks2012. Code I ran to create and save XML for BusinessEntityID = 1:

UPDATE [Person].[Password]
   SET [XML_File] = (SELECT [BusinessEntityID]
      ,[PasswordHash]
      ,[PasswordSalt]
      ,[rowguid]
      ,[ModifiedDate]
      ,[XML_File]
  FROM [Person].[Password]
WHERE BusinessEntityID = 1
FOR XML PATH('Summary'))
WHERE BusinessEntityID = 1

Table view: enter image description here

Created XML:

<Summary>
  <BusinessEntityID>1</BusinessEntityID>
  <PasswordHash>pbFwXWE99vobT6g+vPWFy93NtUU/orrIWafF01hccfM</PasswordHash>
  <PasswordSalt>bE3XiWw=</PasswordSalt>
  <rowguid>329EACBE-C883-4F48-B8B6-17AA4627EFFF</rowguid>
  <ModifiedDate>2003-02-08T00:00:00</ModifiedDate>
</Summary>

So, is there a way of achieving my goal with sql query?

Devart · Accepted Answer · 2016-05-23 14:25:12Z

1

UPDATE p
SET [XML_File] = (
    SELECT [BusinessEntityID]
         , [PasswordHash]
         , [PasswordSalt]
         , [rowguid]
         , [ModifiedDate]
    FOR XML PATH ('Summary')
)
FROM [Person].[Password] p

answered May 23, 2016 at 14:25

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update multiple rows in one column with XML in MS SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related