TSQL XML Manipulation

How to change XML data via TSQL

Modifying XML data via TQL can be done using the following .modify commands...

To insert a new node/attribute...

UPDATE MyTable SET MyXml.modify('insert <item>new</item> as first into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> as last into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> after (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> before (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert attribute attr {"new"} into (/root/item[1])[1]')

To change text/attribute in a node...

UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/text())[1] with "new"')
UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/@attr)[1] with "new"')

To delete a node/attribute/text...

UPDATE MyTable SET MyXml.modify('delete /root/item[1]')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/@attr')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/text()')

Align SQL Login to User

How to re-align a SQL Login to a Database User

In SQL Server there are two distinct security level when it comes to individual users... one at the server level, the other at the database level.

If a database is restored from a different location, then despite the there being a server login and database user with the exact same name, they are different objects and do not therefore align with each other.

Instead of removing either and re-implementing, simply use the following...

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;

SQL XML Ordering

Ordering XML nodes by position in SQL

Passing XML into SQL provides a quick and powerful way of passing complex data into a SQL Server stored procedure.

Unfortunately when inserting new data, you cannot guarantee that it will insert in the actual order of the elements in the XML.

A solution to this is to use ORDER BY ROW_NUMBER...

DECLARE @XML XML = '<data><id>3</id><id>1</id><id>2</id></data>'
INSERT INTO MyTable (ID)
SELECT X.value('text()[1]', 'int')
FROM @XML.nodes('/data/id') AS X(X)
ORDER BY ROW_NUMBER() OVER (ORDER BY X.X)