xml Data Type Representation in the AdventureWorks2008R2 Database
The AdventureWorks2008R2 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 |
|---|---|
There are two types of customers in the AdventureWorks2008R2 database: resellers and individual customers. This column stores additional contact information as XML about the resellers. |
|
Stores reseller survey data. |
|
Stores individual customer survey data. |
|
Stores manufacturing instructions for product models. The instructions for different bicycle models are currently stored. |
|
Stores product model catalog description as XML. |
|
Stores manufacturing illustration diagrams as XML (SVG format). |
|
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 AdventureWorks2008R2 database. For more information about the XML schema collection, see Managing XML Schema Collections on the Server.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2 database:
Person.AdditionalContactInfoSchemaCollection
Sales.IndividualSurveySchemaCollection
HumanResources.HRResumeSchemaCollection
Production.ProductDescriptionSchemaCollection
Production.ManuInstructionsSchemaCollection
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'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.