مشاركة عبر

  • مقالة

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.