--
Creating our XML Datatype table for demo
Create
table DemoXMLTable_WithSchema (id Int, XMLDoc XML)
GO
CREATE
UNIQUE CLUSTERED INDEX demo_
IDX1 ON
DemoXMLTable_WithSchema(id)
GO
CREATE
XML SCHEMA COLLECTION DemoXML_Schema AS
N'<?xml
version="1.0" encoding="UTF-16"?>
<xsd:schema
elementFormDefault="unqualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element
name="employee">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="department" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';
GO
Alter
table DemoXMLTable_WithSchema Alter Column XMLDoc XML (DemoXML_Schema)
GO
With our first step of creating our demo setup is complete lets look at the
components that have gone behind building them.
Find List of Schema in the system:
--
Verify - list of collections in the database
select
* from sys.xml_schema_collections
GO
Find the namespaces used in the system for typed XML data:
Select
* from sys.xml_schema_namespaces
GO
Find schema associated to the XML Column:
SELECT
Object_Name(Object_id) [TableName], c.name, s.name from sys.columns c
INNER
JOIN sys.xml_schema_collections s
ON
c.xml_collection_id = s.xml_collection_id
GO
Find all un-typed XML datatype columns:
SELECT
Object_Name(Object_id) [TableName], c.name from sys.columns c
Where
c.xml_collection_id =0 and system_type_id = 241
GO
Find all the Namespace associated in Schema:
SELECT
n.xml_collection_id, n.name [XML Namespace], s.Name [Schema Name] from
sys.xml_namespaces n
INNER
JOIN sys.xml_schema_collections s
ON
n.xml_collection_id = s.xml_collection_id
GO
Find all the elements in the XML Schema defined:
Select
s.Name, E.name, E.symbol_space, E.must_be_qualified from sys.xml_elements E
INNER
JOIN sys.xml_schema_collections s
ON
E.xml_collection_id = s.xml_collection_id
GO
In the above example you can find that must_be_qualified shows the root element
that needs to be mandatory. Here is where the structured and the un-structured
world meet.
Find all XML Indexes created in the system:
Select
* from sys.xml_indexes
GO
In this FAQ we have taken a tour into some of the system tables that are used
for XML datatypes. This is not the exhaustive list but a good starter for you
to explore the Yukon XML features.