April 18, 2009 at 4:00 pm
I've tried listing all of the AdventureWorks database's XML Schemas, one by one.
First, I've queried the collections using sys.xml_schema_collections. That gave me 6 rows:
select * from sys.xml_schema_collections where schema_id <> schema_id('sys')
Then, by expanding the XML Schema Collections tree in SQL Server Management Studio, I've noticed that some of those collections contained multiple schemas. So I've set off to try and expose them, one by one.
I've ended up writing a query that 'opened up' each collection into the respective schemas, and showed their definitions by means of xml_schema_namespace(). I've called xml_schema_namespace() twice: once to get the collection definition, then on each individual target namespace once each collection item had been parsed out. This query has worked, and has returned the list of 9 individual schemas I wanted to get listed:
create view dbo.xml_schemas
as
select t5.xml_collection_id
, collection_name = t5.name
, schema_id
, xml_namespace_id
, schema_url = v5
, schema_definition = xml_schema_namespace(schema_name(schema_id), t5.name, v5)
from (
select v5 = t4.f4.value('@targetNamespace','varchar(500)')
, t3.*
from (select f3 = t2.f2.query('
, t1.*
from (select f1 = xml_schema_namespace(schema_name(schema_id), name)
, t0.*
from sys.xml_schema_collections t0
where schema_id <> schema_id('sys')
) as t1
cross apply t1.f1.nodes('/') t2(f2)
) as t3
cross apply t3.f3.nodes('declare namespace xsd="http://www.w3.org/2001/XMLSchema";
/root/xsd:schema') t4(f4)
) as t5
inner join sys.xml_schema_namespaces ns
on t5.xml_collection_id = ns.xml_collection_id and t5.v5 = ns.name
You'll notice the output is similar to sys.xml_schema_collections, except it produces 9 rows instead of 6, and it contains two additional columns, containing each of the inner schemas' namespaces together with their individual xml schema definitions.
Now my question is, is there a more compact way of obtaining the same list? I somehow seem to have ended up with lots of subqueries, and I'd like to know if there's a more compact and/or elegant way of doing it.
Thanks.
April 18, 2009 at 5:22 pm
It's OK. I think I've figured out the answer myself:
create view dbo.xml_schemas
as
select t5.xml_collection_id
, collection_name = t5.name
, schema_id
, xml_namespace_id
, schema_url = ns.name
, schema_definition = xml_schema_namespace(schema_name(schema_id), t5.name, ns.name)
from sys.xml_schema_collections t5
inner join sys.xml_schema_namespaces ns
on t5.xml_collection_id = ns.xml_collection_id
where schema_id schema_id('sys')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply