xml Data Type Representation in the AdventureWorks Database

Community Content

In this section

Statistics Annotations (7)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

xml Data Type Representation in the AdventureWorks Database

The AdventureWorks database has several xml type columns. The following table lists the topics in this section that describe these columns. These columns are used in various sample queries in SQL Server Books Online.

Each of these topics provides a sample instance of XML data stored in these columns. Most of these are typed XML columns. These topics provide the XML schema collection that is used to type these columns. To understand the queries specified against them, you should review the XML stored in these columns.

In This Section

Topic	Descriptions
About the Contact.AdditionalContactInfo xml Column	There are two types of customers in the AdventureWorks database: resellers and individual customers. This column stores additional contact information as XML about the resellers.
About the Store.Demographics xml Column	Stores reseller survey data.
About the Individual.Demographics xml Column	Stores individual customer survey data.
About the ProductModel.Instructions xml Column	Stores manufacturing instructions for product models. The instructions for different bicycle models are currently stored.
About the ProductModel.CatalogDescription xml Column	Stores product model catalog description as XML.
About the Illustration.Diagram xml Column	Stores manufacturing illustration diagrams as XML (SVG format).
About the JobCandidate.Resume xml Column	Stores the employee resumes as XML documents.

Most of the columns discussed in this section, except the Diagram column in the Illustration table, are typed xml columns. For more information, see Typed XML Compared to Untyped XML. To see the XML schemas that are used for these columns, go to this Microsoft Web site.

The following query returns a list of XML schema collections from the AdventureWorks database. For more information about the XML schema collection, see Managing XML Schema Collections on the Server.

USE AdventureWorks
GO
SELECT *
FROM  sys.xml_schema_collections

-- Result shows the list of XML schema collection names.
AdditionalContactInfoSchemaCollection
IndividualSurveySchemaCollection
HRResumeSchemaCollection
ProductDescriptionSchemaCollection
ManuInstructionsSchemaCollection
StoreSurveySchemaCollection

The following query returns the XML schema collection names with the relational schema name:

SELECT xsc.xml_collection_id, 
       s.name + '.' + xsc.name as xml_collection, 
       xsc.principal_id, xsc.create_date, xsc.modify_date
FROM sys.xml_schema_collections xsc 
     JOIN sys.schemas s
     ON xsc.schema_id = s.schema_id

The xml_collection column in the result returns the following XML schema collections in the AdventureWorks database:

Person.AdditionalContactInfoSchemaCollection
Sales.IndividualSurveySchemaCollection
HumanResources.HRResumeSchemaCollection
Production.ProductDescriptionSchemaCollection
Production.ManuInstructionsSchemaCollection
Sales.StoreSurveySchemaCollection
sys.sys

For more information about the sys.sys XML schema collection, see Built-in XML Schema Collection (sys).

You can use the xml_schema_namespace intrinsic function to reconstruct the XML schema collection as follows:

SELECT xml_schema_namespace(N'Person',N'AdditionalContactInfoSchemaCollection')
GO
SELECT xml_schema_namespace(N'Sales',N'IndividualSurveySchemaCollection')
GO
SELECT xml_schema_namespace(N'HumanResources',N'HRResumeSchemaCollection')
GO
SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection')
Go
SELECT xml_schema_namespace(N'Production',N'ManuInstructionsSchemaCollection')
GO
SELECT xml_schema_namespace(N'Sales',N'StoreSurveySchemaCollection')
GO

For more information, see Viewing a Stored XML Schema Collection.

Reference

Concepts

Other Resources

Help and Information