How to create XML Schema Collection in SQL Server

sql server create xml schema collection from file and sql server schema name best practices and sql server schema name vs database name
Dr.JeanHiggs Profile Pic
Dr.JeanHiggs,United States,Professional
Published Date:10-08-2017
Your Website URL(Optional)
Comment
SQL SERVER SCHEMA COLLECTIONS AND METADATA SQL Server maintains XML schemas in schema collections. A schema collection is a system object just like tables, stored procedures, indexes, etc. A schema collection can store more than one schema definition. In this chapter we will examine XML Schema Collections in detail. We will discuss:  Why is there a collection?  How to create schema 'collections'  How to alter schema collections  Retrieving schema definition from SQL Server  Adding constraints using facets: CONTENT and DOCUMENT  XML Schema Collection Metadata  Limitations of SQL Server Schema Collections Why schema 'collection'? It is not clear to me why there is no xml schema object in SQL Server. Instead, what we have is xml schema collection objects. Each xml schema collection object stores one or more schema definitions, so you need an xml schema collection even for storing a single schema definition. Though a schema collection can store more than one schema definition, most of the time you would prefer to store only one schema definition in it. When you validate an XML instance against a schema collection, SQL Server will validate the XML instance against all the global elements declared in the entire schema collection until a suitable match is found. If the XML instance validates against any of the global elements declared in the entire schema collection, the insert/update/assignment operation will succeed. 449 14 – SQL Server schema collections and metadata A Schema Collection Example Let us look at a schema collection having more than one schema definition. Let us think of an application used by a training school. They have a table named "person" that stores information of students as well as trainers. Assume that they need to store some additional information which is specific to the type of data stored in each row. For example, if the row stores information regarding a student they need to store the course and duration of the course in which he or she is enrolled. If the row stores information of a trainer, they need to store the qualification of the trainer and the details of industry experience he or she has. Let us try to use an XML column to store this additional information. Let us create a schema collection that contains two schema definitions: one to validate student information and the other to validate trainer information. Here is an example of student information. Student CourseSQL Server Training/Course Duration6 Months/Duration /Student Listing 14.1 And the following example shows the structure of the trainer information XML document. Trainer QualificationMCDBA/Qualification IndustryExperience10 Years/IndustryExperience /Trainer Listing 14.2 Let us create a schema collection containing the schema definition to validate student information. CREATE XML SCHEMA COLLECTION StudentOrTrainer AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" xsd:complexType xsd:sequence xsd:element name="Course"/ xsd:element name="Duration"/ /xsd:sequence 450 14 – SQL Server schema collections and metadata /xsd:complexType /xsd:element /xsd:schema' GO Listing 14.3 You can add a new schema definition to an existing schema collection by using ALTER SCHEMA COLLECTION ADD command. Let us add the schema definition of the trainer information to the above schema collection. ALTER XML SCHEMA COLLECTION StudentOrTrainer ADD ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" xsd:complexType xsd:sequence xsd:element name="Qualification"/ xsd:element name="IndustryExperience"/ /xsd:sequence /xsd:complexType /xsd:element /xsd:schema' GO Listing 14.4 Since this schema collection has the definition for a Student element as well as Trainer element, this will accept both XML instances that we saw earlier. DECLARE x XML(StudentOrTrainer) Student Information SELECT X = ' Student CourseSQL Server Training/Course Duration6 Months/Duration /Student' Trainer Information SELECT X = ' Trainer QualificationMCDBA/Qualification IndustryExperience10 Years/IndustryExperience /Trainer' Listing 14.5 The first schema definition has a global element declaration named Student. The second schema definition contains a global element declaration named Trainer. By adding both schema definitions to the same 451 14 – SQL Server schema collections and metadata schema collection, we could make sure that SQL Server will accept XML instances that validates against either one of the global elements declared in the schema collection. It can also accept an XML instance that contains both Student and Trainer elements. The following XML instance will validate successfully. DECLARE x XML(StudentOrTrainer) Student Information SELECT X = ' Student CourseSQL Server Training/Course Duration6 Months/Duration /Student Trainer QualificationMCDBA/Qualification IndustryExperience10 Years/IndustryExperience /Trainer' Listing 14.6 Though we created two separate schema definitions and added them to the same schema collection, SQL Server will merge the definitions and will internally maintain only one schema definition. So the code shown in the following two code snippets are equivalent. CREATE XML SCHEMA COLLECTION StudentOrTrainer AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" xsd:complexType xsd:sequence xsd:element name="Course"/ xsd:element name="Duration"/ /xsd:sequence /xsd:complexType /xsd:element /xsd:schema' GO ALTER XML SCHEMA COLLECTION StudentOrTrainer ADD ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" xsd:complexType xsd:sequence xsd:element name="Qualification"/ xsd:element name="IndustryExperience"/ /xsd:sequence 452 14 – SQL Server schema collections and metadata /xsd:complexType /xsd:element /xsd:schema' GO Listing 14.7: This example shows a schema collection that contains the definition of two global elements. Each element is added separately to the schema collection. The above schema collection is equivalent to the following. CREATE XML SCHEMA COLLECTION StudentOrTrainer AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" xsd:complexType xsd:sequence xsd:element name="Course"/ xsd:element name="Duration"/ /xsd:sequence /xsd:complexType /xsd:element Trainer Element xsd:element name="Trainer" xsd:complexType xsd:sequence xsd:element name="Qualification"/ xsd:element name="IndustryExperience"/ /xsd:sequence /xsd:complexType /xsd:element /xsd:schema' GO Listing 14.8: This example shows a schema collection having the definition of two global elements. When you add a new schema definition to an existing schema collection, SQL Server will merge the schema definitions and group them by target namespace. We will see this later in this chapter. Altering a schema collection I had mentioned several times earlier that you cannot alter a schema collection. Well, you can alter a schema collection to add more schema components. You can use ALTER XML SCHEMA COLLECTION command to add new components to an existing schema collection. We have seen an example of this in the previous section. 453 14 – SQL Server schema collections and metadata Though you can add a new schema component to an existing schema collection, you cannot modify the definition of existing schema components. The following example creates a schema collection with a Student element. DROP the previous SCHEMA COLLECTION IF EXISTS( SELECT FROM sys.xml_schema_collections WHERE name = 'ExampleSchema' ) BEGIN DROP XML SCHEMA COLLECTION ExampleSchema END GO Create a schema collection with 'Student' element CREATE XML SCHEMA COLLECTION ExampleSchema AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" type="xsd:string" / /xsd:schema' GO Listing 14.9 You can alter this schema collection and add additional components. For example, the following code adds a Trainer element to this schema collection. Add a new element 'trainer' ALTER XML SCHEMA COLLECTION ExampleSchema ADD ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" type="xsd:string" / /xsd:schema' GO Listing 14.10 However, you cannot alter the definition of the Student element. Let us try to change the type of Student element. Add a new element 'trainer' ALTER XML SCHEMA COLLECTION ExampleSchema ADD ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Student" type="xsd:token" / /xsd:schema' GO Listing 14.11 This will not run. SQL Server will generate the following error if you try to run this code. 454 14 – SQL Server schema collections and metadata Altering existing schema components is not allowed. There was an attempt to modify an existing XML Schema component, component namespace: '' component name: 'Student' component kind:ELEMENT If you want to modify the definition of an existing schema component, you need to drop the schema collection and create the schema collection again with the updated definition. But SQL Server will not allow you to drop a schema collection if the schema collection is bound to an XML column or a stored procedure or function has a TYPED XML parameter bound to the given schema collection. So before you drop a schema collection, you need to remove all references to the given schema collection from columns, functions and stored procedures, etc. This makes modification of schema definitions very difficult. You can remove the binding of a schema collection with a column using the following TSQL code. ALTER TABLE tableName ALTER COLUMN columnName XML Listing 14.12 The above code changes the column to an UNTYPED XML column. You can also alter the column to some other XML schema collection if all the values in the given column are valid for the other schema collection. Let us try to understand this by looking at an example. Create the following schema collection that validates student information. CREATE XML SCHEMA COLLECTION StudentSchema AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" type="xsd:string" / /xsd:schema' GO Listing 14.13 Let us create one more schema collection that validates trainer information. 455 14 – SQL Server schema collections and metadata CREATE XML SCHEMA COLLECTION TrainerSchema AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" type="xsd:string" / /xsd:schema' GO Listing 14.14 Let us create a table having a TYPED XML column bound to the SudentSchema schema collection. CREATE TABLE Person(PersonName VARCHAR(20), Data XML(StudentSchema)) Listing 14.15 Let us insert a few rows to the above table. INSERT INTO Person(PersonName, Data) SELECT 'Jacob', 'StudentSome StudentInfo/Student' INSERT INTO Person(PersonName, Data) SELECT 'Mike', 'StudentSome More StudentInfo/Student' Listing 14.16 At this point, the schema collection StudentSchema is bound to the Data column of Person table. I had mentioned earlier that, if you want to alter the definition of an existing schema collection, you need to drop the schema collection and create it again with the new definition. Let us try to drop this schema collection and see if SQL Server allows that. DROP XML SCHEMA COLLECTION StudentSchema Listing 14.17 SQL Server will not allow you to drop this schema collection because it is bound to a column. If you try to run the above code, you will get the following error. Specified collection 'StudentSchema' cannot be dropped because it is used by object 'dbo.Person'. So before you drop the schema collection, you need to change the schema binding. You need to remove the binding between the schema collection 456 14 – SQL Server schema collections and metadata and the XML columns as well as the TYPED XML parameters of functions and stored procedures. You can run the following code to unbind the Data column. ALTER TABLE Person ALTER COLUMN Data XML Listing 14.18 This will set the column to an UNTYPED XML column. Alternatively, you can bind the column to another schema collection if the data in the column validates with that schema collection. When you alter the definition of an XML column and bind it to another XML schema collection, SQL Server will validate the values in the column with the new schema collection. The ALTER operation will succeed only if all the values in the column validate successfully with the new schema collection. For example, the following will fail. ALTER TABLE Person ALTER COLUMN Data XML(TrainerSchema) Listing 14.19 The values currently stored in the "Data" column contain XML fragments that validate with StudentSchema but not with TranerSchema; hence, the ALTER operation will fail with the following error. XML Validation: Declaration not found for element 'Student'. Location: /:Student1 The statement has been terminated. Just as in the case with columns, SQL Server will not allow you to drop a schema collection if any of the stored procedures or functions has a TYPED XML parameter bound to the given schema collection. Before you can drop the schema collection, you need to remove such references either by turning the parameters to UNTYPED XML or changing them to anther schema collection. To summarize, altering schema collections is not very easy. You need to be very careful while writing your schema definitions so that frequent alterations of schema collections can be avoided. 457 14 – SQL Server schema collections and metadata Retrieving Schema Definition Sometimes you might need to retrieve the definition of a schema collection from SQL Server. You can use xml_schema_namespace() function to retrieve the definition of a schema collection stored internally by SQL Server. To experiment with this, let us create a new schema collection. DROP the previous SCHEMA COLLECTION IF EXISTS( SELECT FROM sys.xml_schema_collections WHERE name = 'ExampleSchema' ) BEGIN DROP XML SCHEMA COLLECTION ExampleSchema END GO CREATE XML SCHEMA COLLECTION ExampleSchema AS' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" type="xsd:string" / /xsd:schema' GO Listing 14.20 Let us try to retrieve the definition of this schema collection from SQL Server. SELECT xml_schema_namespace('dbo','ExampleSchema') Listing 14.21 The above code returns the following. xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:element name="Student" type="xsd:string" / /xsd:schema Listing 14.22: Schema definition retrieved from SQL Server. Let us alter the schema collection and add one more element. 458 14 – SQL Server schema collections and metadata ALTER XML SCHEMA COLLECTION ExampleSchema ADD' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" / /xsd:schema' GO Listing 14.23 This will add a new element to the schema collection that we created earlier. Now let us try to retrieve the modified schema definition from SQL Server. SELECT xml_schema_namespace('dbo','ExampleSchema') xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:element name="Student" type="xsd:string" / xsd:element name="Trainer" type="xsd:anyType" / /xsd:schema Listing 14.24: Schema definition retrieved from SQL Server. Note that SQL Server will strip off comments from the schema definition. Further, SQL Server will apply small modifications to your schema. In the above example, we did not specify the type attribute for the Trainer element and SQL Server added the Type attribute and set it to anyType. Such modifications do not alter the meaning of your schema, nor do they change the validation rules. When you retrieve the definition of a schema collection for further modification you might find it bit confusing, because SQL Server will give you a slightly modified version of the original schema. Further, all the comments you had written will be gone, too. To make editing schema collections easier, you need to keep a copy of your schema definitions in an XML file or in a table. When you need to modify a schema collection, you can take the version of the schema that you have (in files or a table), modify it and recreate the schema collection. It is possible to create a schema collection from a variable. Hence, it is possible to create a stored procedure that reads schema definition from a file or from a table and creates the schema collections. The following code snippets show how to create schema collections from a variable, from a table or from a disk file. 459 14 – SQL Server schema collections and metadata DECLARE xml XML SELECT xml = ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" type="xsd:string" / /xsd:schema' CREATE XML SCHEMA COLLECTION SchemaFromVariable AS xml GO Listing 14.25: Creating a schema collection from a variable. DECLARE xml XML Load the schema definition from a file SELECT xml =CONVERT(XML, bulkcolumn, 2) FROM OPENROWSET(BULK 'C:\temp\SchemaFile.xml', SINGLE_BLOB) AS x create schema collection CREATE XML SCHEMA COLLECTION SchemaFromFile AS xml GO Listing 14.26: Creating a schema collection from the definition taken from a file. DECLARE xml XML Load the schema definition from a table SELECT xml = SchemaDefinition FROM SchemaTable WHERE SchemaName = 'SchemaFromTable' create schema collection CREATE XML SCHEMA COLLECTION SchemaFromTable AS xml GO Listing 14.27: Creating a schema collection from the definition taken from a table. Multiple Target Namespaces In the previous chapter we examined the targetNamespace attribute of schema declaration. This attribute is used to specify a namespace to which the elements in the XML instance should belong. When you add more than one schema definition to a single schema collection, SQL Server groups schema definitions by target namespace. Let us revisit one of the examples we saw earlier in this chapter. 460 14 – SQL Server schema collections and metadata The following example shows a schema collection that stores student information. CREATE XML SCHEMA COLLECTION ExampleSchema AS' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Student Element xsd:element name="Student" type="xsd:string" / /xsd:schema' GO Listing 14.28 After creating the schema collection for storing student information, we added one more schema definition to the schema collection by using ALTER XML SCHEMA COLLECTION command. ALTER XML SCHEMA COLLECTION ExampleSchema ADD' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" Trainer Element xsd:element name="Trainer" / /xsd:schema' GO Listing 14.29 When we added the second schema definition to the existing schema collection, SQL Server grouped the schema definitions together. We received the following schema when we retrieved the definition of the schema collection from SQL Server. xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:element name="Student" type="xsd:string" / xsd:element name="Trainer" type="xsd:anyType" / /xsd:schema Listing 14.30: Definition of a schema collection retrieved from SQL Server. Note that SQL Server merged the schema definitions to a single schema. This merging is done based on the target namespace declaration of each schema component. In the above example, both schema definitions did not have a target namespace declaration. Hence, SQL Server merged both schema definitions to a single schema. Now let us see what happens if the schema definitions have different target namespace declarations. 461 14 – SQL Server schema collections and metadata DROP the previous SCHEMA COLLECTION IF EXISTS( SELECT FROM sys.xml_schema_collections WHERE name = 'ExampleSchema' ) BEGIN DROP XML SCHEMA COLLECTION ExampleSchema END GO Create a schema collection with 'Student' element CREATE XML SCHEMA COLLECTION ExampleSchema AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.student.com" xsd:element name="Student" type="xsd:string" / /xsd:schema' GO Listing 14.31 Note that the student information element has a targetNamespace declaration that insists that the XML instance have the specified namespace declaration. Now let us add the declaration of the Trainer element having a different target namespace declaration. ALTER XML SCHEMA COLLECTION ExampleSchema ADD ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.trainer.com" xsd:element name="Trainer" type="xsd:string" / /xsd:schema' GO Listing 14.32 The Trainer element is declared with a different namespace declaration. The following are the valid XML instances that validate with the above version of the schema collection. DECLARE x XML(ExampleSchema) student information SELECT x = ' Student xmlns="http://www.student.com" Student Info /Student' trainer information SELECT x = ' Trainer xmlns="http://www.trainer.com" Trainer Info /Trainer' Both student and trainer information 462 14 – SQL Server schema collections and metadata SELECT x = ' Student xmlns="http://www.student.com" Student Info /Student Trainer xmlns="http://www.trainer.com" Trainer Info /Trainer' Listing 14.33 Now, let us retrieve the definition of this schema collection from SQL Server and see what it looks like. SELECT xml_schema_namespace('dbo','ExampleSchema') xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://www.student.com" targetNamespace="http://www.student.com" xsd:element name="Student" type="xsd:string" / /xsd:schema xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://www.trainer.com" targetNamespace="http://www.trainer.com" xsd:element name="Trainer" type="xsd:string" / /xsd:schema Listing 14.34 You will find this schema slightly different from what we have seen previously. Again, you will see that it is a bit different from the schema definition you provided when you created the schema collection. Note the additional declaration of the namespace with prefix: "t." As I mentioned earlier in this chapter, SQL Server will slightly modify your schema definition without altering the meaning of your schema collection. It looks like SQL Server shreds the XML schema definition to a set of relational tables internally and re-assembles the schema when you call the xml_schema_namespace() function. The above example shows that SQL Server groups schema definitions by target namespace. If you would like to retrieve the schema definition having a specific target namespace declaration, you can do it by specifying the target namespace as the third argument to the xml_schema_namespace() function. The following example retrieves the schema definition of the student element. SELECT xml_schema_namespace('dbo','ExampleSchema', 'http://www.student.com') xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://www.student.com" 463 14 – SQL Server schema collections and metadata targetNamespace="http://www.student.com" xsd:element name="Student" type="xsd:string" / /xsd:schema Listing 14.35 You can achieve the same result by running the following XQuery method as well. SELECT xml_schema_namespace('dbo','ExampleSchema').query(' /xs:schematargetNamespace="http://www.student.com" ') xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://www.student.com" targetNamespace="http://www.student.com" xsd:element name="Student" type="xsd:string" / /xsd:schema Listing 14.36 Adding constraints using facets A TYPED XML column or variable has two facets that control the type of XML value it can store. There are two facets: CONTENT and DOCUMENT. When you declare a TYPED XML variable or column, SQL Server automatically associates the CONTENT facet to it. When a TYPED XML column or variable is declared with CONTENT facet, it can store XML values having multiple top level elements. When it is declared with DOCUMENT facet, the XML value should have a single root element. The following XML instance contains multiple top level elements. This can be stored to a TYPED XML column or variable declared with CONTENT facet. Employee NameJacob/Name /Employee Employee NameMike/Name /Employee Listing 14.37 The XML instance given below has a single root element and can be stored to a TYPED XML column or variable declared with CONTENT or DOCUMENT facet. 464 14 – SQL Server schema collections and metadata Employee NameMike/Name /Employee Listing 14.38 Let us create a schema collection that describes the above XML instance. DROP the previous SCHEMA COLLECTION IF EXISTS( SELECT FROM sys.xml_schema_collections WHERE name = 'ExampleSchema' ) BEGIN DROP XML SCHEMA COLLECTION ExampleSchema END GO Create a schema collection CREATE XML SCHEMA COLLECTION ExampleSchema AS ' xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:element name="Employee" xsd:complexType xsd:sequence xsd:element name="Name"/ /xsd:sequence /xsd:complexType /xsd:element /xsd:schema' GO Listing 14.39 Let us declare an XML variable with CONTENT facet. DECLARE x XML(CONTENT ExampleSchema) SELECT x = ' Employee NameJacob/Name /EmployeeEmployee NameMike/Name /Employee' Listing 14.40 If you have not specified a facet, SQL Server will associate all TYPED XML columns and variables with CONTENT facet. Hence, the following is equivalent to the above example. DECLARE x XML(ExampleSchema) SELECT x = ' Employee NameJacob/Name /Employee 465 14 – SQL Server schema collections and metadata Employee NameMike/Name /Employee' Listing 14.41 The example given below declares a typed XML variable with DOCUMENT facet. DECLARE x XML(DOCUMENT ExampleSchema) SELECT x = ' Employee NameMike/Name /Employee' Listing 14.42 A TYPED XML column or variable having DOCUMENT facet can only store XML instances having a single root element. If you try to store XML instances with more than one top level element, SQL server will raise an error. DECLARE x XML(DOCUMENT ExampleSchema) SELECT x = ' Employee NameJacob/Name /Employee Employee NameMike/Name /Employee' Listing 14.43 If you try to run this code, SQL Server will generate the following error. XML Validation: XML instance must be a document. The following example creates a table having two TYPED XML columns: one with CONTENT facet and the other DOCUMENT facet. CREATE TABLE FacetTest ( documentData XML(DOCUMENT ExampleSchema), contentData XML(CONTENT ExampleSchema) ) GO Listing 14.44 466 14 – SQL Server schema collections and metadata XML Schema Collection Metadata SQL Server maintains information about schema collections in system metadata. There are a number of system catalog views that you can use to query information related to your schema collections. The following are the system catalog views that provide information about XML Schema Collections.  sys.xml_schema_collections  sys.xml_schema_components  sys.xml_schema_elements  sys.xml_schema_attributes  sys.xml_schema_model_groups  sys.xml_schema_types  sys.xml_schema_wildcards  sys.xml_schema_namespaces  sys.xml_schema_wildcard_namespaces  sys.xml_schema_facets  sys.xml_schema_component_placements  sys.column_xml_schema_collection_usages  sys.parameter_xml_schema_collection_usages  sys.xml_indexes Let us examine each of these catalog views briefly. Please refer to Books Online for a detailed explanation of the columns returned by each catalog view. SQL Server 2005: http://msdn.microsoft.com/en-us/library/ ms189815(SQL.90).aspx SQL Server 2008: http://msdn.microsoft.com/en-us/library/ ms189815.aspx 467 14 – SQL Server schema collections and metadata sys.xml_schema_collections This catalog view retrieves all the schema collections in the current database. This view will have one row per schema collection. To retrieve the information about the ExampleSchema we created earlier, run the following query. SELECT FROM sys.xml_schema_collections c WHERE c.name = 'ExampleSchema' Listing 14.45 sys.xml_schema_components This view returns a row per XML schema component. It includes all the elements, attributes, model groups, simple types, complex types, etc. You can run the following query to retrieve the details of all the schema components of a given schema collection. SELECT sc. FROM sys.xml_schema_components sc INNER JOIN sys.xml_schema_collections c ON sc.xml_collection_id = c.xml_collection_id WHERE c.name = 'ExampleSchema' Listing 14.46 sys.xml_schema_elements This view returns a row per xml schema element declaration. The information this view returns is a subset of the information provided by the catalog view: xml_schema_components. You can join this view with sys.xml_schema_collections and retrieve all the elements declared in a given schema collection. Here is an example: SELECT e. FROM sys.xml_schema_elements e INNER JOIN sys.xml_schema_collections c ON e.xml_collection_id = c.xml_collection_id WHERE c.name = 'ExampleSchema' Listing 14.47 468

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.