Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML

3/1/2011 11:42:57 AM


The XQuery specification does not provide a way for you to modify data, as you would have with the INSERT, UPDATE, and DELETE keywords in T-SQL. The W3C has a working draft, but it might be a few years before it becomes a standard, so Microsoft has created its own XML data manipulation language, XML DML, which is included in its XQuery implementation. The Microsoft version conforms to the W3C working draft, but by no means do we know the final standard’s look and feel.

XML DML gives you three ways to manipulate the XML data of a column via the xml.modify() function:

  • xml.modify(insert) Allows you to insert a node or sequence of nodes into the XML data type instance you are working with

  • xml.modify(delete) Allows you to delete zero or more nodes that are the result of the output sequence of the XQuery expression you specify

  • xml.modify(replace) Modifies the value of a single node


The xml.modify(insert) function allows you to insert a node or sequence of nodes into the XML data type instance you are working with. You use the xml.modify()UPDATE statement and, if necessary, a T-SQL or XQuery where clause (or both). You can also specify the keyword first or last beside an ordinal position. function in conjunction with a T-SQL

--insert an XML node
Update tblSpeakers
Set Speaker_XML.modify(
<class name="Ranking and Windowing Functions in SQL Server 2005"/>
into /classes[1]'
Where Speaker_ID=1


The xml.modify(delete) function deletes zero or more nodes based on the criteria you specify. The following example deletes a specific node based on its ordinal position; you can also combine this with a T-SQL or XQuery WHERE clause.

Update tblSpeakers
Set Speaker_XML.modify('delete /classes/class[3]')
Where Speaker_ID=1

--modify a value
Update tblSpeakers
Set Speaker_XML.modify('
replace value of
to "Protecting against SQL Injection Attacks-Hackers must die"')
Where Speaker_ID=1

Converting a Column to XML

If you have existing text or varchar() columns in SQL Server 2000 that contain XML data, you’re probably excited about using the XML data type and XQuery. Because you can use the T-SQL CONVERT and CAST functions to convert those columns into full-fledged XML data, you can write a simple upgrade script.

First we need a table. For this example, we'll create a simple table:
Create table tblXMLUpgradeTest
(Field_ID int Primary Key Identity,
Field_XMLData nvarchar(4000))

Next we insert some data, but remember that it is stored as text, not XML.

--insert some data as txt but is XML
--remember no cool XQuery or anything allowed!
DECLARE @xmlData AS varchar(8000)
SET @xmlData = (Select Customer.CustomerID,
From Sales.Customer Customer
inner join Sales.SalesOrderHeader OrderDetail
on OrderDetail.customerid=Customer.customerid
where Customer.CustomerID<5
Order by Customer.CustomerID
For XML AUTO, Root ('Orders'))
--first insert into the table
Insert Into tblXMLUpgradeTest (Field_XMLData) Values (@xmlData)

You can now convert your XML data from text to an XML column in a series of six steps:

In the table containing the text field holding the XML data, add a varchar(8000) or text field, depending on what your original column is.

Append the data in your current production text-based column into the new XML-based column.

Drop your text-based column.

Add the original column name back in, but with an XML data type.

Insert the data from the temporary column, using the CONVERT statement to convert the data to an XML data type.

Drop the temporary column.

A code example of all six steps is shown here.

--first alter the table
ALTER TABLE tblXMLUpgradeTest ADD Field_XMLData_Temp varchar(8000)
--second update the Field_XMLData_Temp column
Update tblXMLUpgradeTest set Field_XMLData_Temp=Field_XMLData
--third drop the original text column (Field_XMLData)
ALTER TABLE tblXMLUpgradeTest Drop Column Field_XMLData
--fourth add the same column(Field_XMLData) but as XML
--fifth insert the XML into Field_XMLData and convert to XML data type
Update tblXMLUpgradeTest set Field_XMLData=Convert(XML, Field_XMLData_Temp)
--sixth drop the Field_XMLData_Temp column since it has text, not XML
ALTER TABLE tblXMLUpgradeTest Drop Column Field_XMLData_Temp
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
  •  SQL Server 2008 : Audit-Related Startup and Shutdown Problems
  •  SQL Server 2008 : Creating SQL Server Audits Using the GUI
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone