Introduction
The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. Note that the stored representation of xml data type instances cannot exceed 2 GB.
Paragraph Heading 1
The xml data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT.
You can use XQuery to query XML instances stored in columns, parameters, or variables. You can also use the XML Data Manipulation Language (XML DML) to apply updates to the XML instances. Because the XQuery standard did not define XQuery DML at the time of development, SQL Server introduces XML Data Modification Language extensions to XQuery. These extensions allow you to perform insert, update, and delete operations.
Paragraph Heading N
When you create columns of xml type, you can define column-level or table-level constraints. However, you cannot use the XML data type methods when you specify constraints.
Modifying Tables
The ALTER TABLE statement supports the xml data type. For example, you can alter any string type column to the xml data type. Note that in these cases, the documents contained in the column must be well formed. Also, if you are changing the type of the column from string to typed xml, the documents in the column are validated against the specified XSD schemas.
Examples
Creating Views
You can use an xml type column to create views. The following example creates a view in which the value from an xml type column is retrieved using the value() method of the xml data type.
-- Create the table.
CREATE TABLE T (
ProductID int primary key,
CatalogDescription xml)
GO
-- Insert sample data.
INSERT INTO T values(1,'')
GO
-- Create view (note the value() method used to retrieve ProductName
-- attribute value from the XML).
CREATE VIEW MyView AS
SELECT ProductID,
CatalogDescription.value('(/ProductDescription/@ProductName)[1]', 'varchar(40)') AS PName
FROM T
GO
Using XML in Computed Columns
XML instances can appear as a source for a computed column, or as a type of computed column. For example, in the following CREATE TABLE statement, an xml type column (col2) is computed from col1:
CREATE TABLE T(col1 varchar(max), col2 AS CAST(col1 AS xml) )
The xml data type can also appear as a source in creating a computed column, as shown in the following CREATE TABLE statement:
CREATE TABLE T (col1 xml, col2 as cast(col1 as varchar(1000) ))
You can create a computed column by extracting a value from an xml type column as shown in the following example. Because the xml data type methods cannot be used directly in creating computed columns, the example first defines a function (my_udf) that returns a value from an XML instance. The function wraps the value() method of the xml type. The function name is then specified in the CREATE TABLE statement for the computed column.
CREATE FUNCTION my_udf(@var xml) returns int
AS BEGIN
RETURN @var.value('(/ProductDescription/@ProductModelID)[1]' , 'int')
END
GO
-- Use the function in CREATE TABLE.
CREATE TABLE T (col1 xml, col2 as dbo.my_udf(col1) )
GO
-- Try adding a row.
INSERT INTO T values('')
GO
-- Verify results.
SELECT col2, col1
FROM T
As in the previous example, the following example defines a function to return an xml type instance for a computed column. Inside the function, the query() method of the xml data type retrieves a value from an xml type parameter.
CREATE FUNCTION my_udf(@var xml)
RETURNS xml AS
BEGIN
RETURN @var.query('ProductDescription/Features')
END
In the following CREATE TABLE statement, Col2 is a computed column that uses the XML data ( element) that is returned by the function:
CREATE TABLE T (Col1 xml, Col2 as dbo.my_udf(Col1) )
-- Insert a row in table T.
INSERT INTO T VALUES('
description
description
')
-- Verify the results.
SELECT *
FROM T
|
No responses found. Be the first to respond and make money from revenue sharing program.
|