Skip to content

Latest commit

 

History

History
61 lines (49 loc) · 1.91 KB

create-views-over-xml-columns.md

File metadata and controls

61 lines (49 loc) · 1.91 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Create views over XML columns
Learn how to create a view in which the value from an xml type column is retrieved using the value() method of the xml data type.
MikeRayMSFT
mikeray
randolphwest
05/05/2022
sql
xml
how-to
views [XML in SQL Server]

Create views over XML columns

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

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,'<ProductDescription ProductID="1" ProductName="SomeName" />');
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

Execute the following query against the view:

SELECT *
FROM   MyView;

This is the result:

ProductID   PName
----------- ------------
1           SomeName

Note the following points about using the xml data type to create views:

  • The xml data type can be created in a materialized view. The materialized view can't be based on an xml data type method. However, it can be cast to an XML schema collection that is different from the xml type column in the base table.

  • The xml data type can't be used in Distributed Partitioned Views.

  • SQL predicates running against the view won't be pushed into the XQuery of the view definition.

  • xml data type methods in a view aren't updatable.