Skip to content

Latest commit

 

History

History
55 lines (45 loc) · 2.53 KB

change-existing-columns-to-xml-columns.md

File metadata and controls

55 lines (45 loc) · 2.53 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Change existing columns to XML columns
Learn how to use the ALTER TABLE statement to change a string type column to an xml data type column.
MikeRayMSFT
mikeray
randolphwest
05/05/2022
sql
xml
conceptual
tables [XML]

Change existing columns to XML columns

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

The ALTER TABLE statement supports the xml data type. For example, you can alter any string type column to the xml data type. In these cases, the documents contained in the column must be well formed. Also, if you're changing the type of the column from string to typed xml, the documents in the column are validated against the specified XSD schemas.

CREATE TABLE T (Col1 int primary key, Col2 nvarchar(max));
GO
INSERT INTO T
  VALUES (1, '<Root><Product ProductID="1"/></Root>');
GO
ALTER TABLE T
  ALTER COLUMN Col2 xml;

You can change an xml type column from untyped XML to typed XML. For example:

CREATE TABLE T (Col1 int primary key, Col2 xml);
GO
INSERT INTO T
  values (1, '<p1:ProductDescription ProductModelID="1"
xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
            </p1:ProductDescription>');
GO
-- Make it a typed xml column by specifying a schema collection.
ALTER TABLE T
  ALTER COLUMN Col2 xml (Production.ProductDescriptionSchemaCollection);

Note

The script will run against [!INCLUDEssSampleDBobject] database, because the XML schema collection, Production.ProductDescriptionSchemaCollection, is created as part of the [!INCLUDEssSampleDBobject] database.

In the previous example, all the instances stored in the column are validated and typed against the XSD schemas in the specified collection. If the column contains one or more XML instances that are invalid regarding the specified schema, the ALTER TABLE statement will fail and you won't be able to change your untyped XML column into typed XML.

Note

If a table is large, modifying an xml type column can be costly. This is because each document must be checked for being well formed and, for typed XML, must also be validated.

See also