Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Using XML Schemas

 

Applies To: SQL Server 2016 Preview

XML programming in SMO is limited to providing XML data types, XML namespaces, and simple indexing on XML data type columns.

Microsoft SQL Server provides native storage for XML document instances. XML schemas let you define complex XML data types, which can be used to validate XML documents to ensure data integrity. The XML schema is defined in the XmlSchemaCollection object.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see Create a Visual Basic SMO Project in Visual Studio .NET or Create a Visual C# SMO Project in Visual Studio .NET.

This code example shows how to create an XML schema by using the XmlSchemaCollection object. The Text property, which defines the XML schema collection, contains several double quotation marks. These are replaced by the chr(34) string.

Code snippet is not found. Confirm that the code snippet name 'SMO How to' is correct.

This code example shows how to create an XML schema by using the XmlSchemaCollection object. The Text property, which defines the XML schema collection, contains several double quotation marks. These are replaced by the chr(34) string.

{
            //Connect to the local, default instance of SQL Server. 
            Server srv = default(Server);
            srv = new Server();
            //Reference the AdventureWorks2012 database. 
            Database db = default(Database);
            db = srv.Databases["AdventureWorks2012"];
            //Define an XmlSchemaCollection object by supplying the parent
            // database and name arguments in the constructor. 
            XmlSchemaCollection xsc = default(XmlSchemaCollection);
            xsc = new XmlSchemaCollection(db, "MySampleCollection");
            xsc.Text = "<schema xmlns=" + Strings.Chr(34) + "http://www.w3.org/2001/XMLSchema" + Strings.Chr(34) + " xmlns:ns=" + Strings.Chr(34) + "http://ns" + Strings.Chr(34) + "><element name=" + Strings.Chr(34) + "e" + Strings.Chr(34) + " type=" + Strings.Chr(34) + "dateTime" + Strings.Chr(34) + "/></schema>";
            //Create the XML schema collection on the instance of SQL Server. 
            xsc.Create();
        }

This code example shows how to create an XML schema by using the XmlSchemaCollection object. The Text property, which defines the XML schema collection, contains several double quotation marks. These are replaced by the chr(34) string.

#Get a server object which corresponds to the default instance replace LocalMachine with the physical server
cd \sql\LocalHost
$srv = get-item default

#Reference the AdventureWorks database.
$db = $srv.Databases["AdventureWorks2012"]

#Create a new schema collection
$xsc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.XmlSchemaCollection `
-argumentlist $db,"MySampleCollection"

#Add the xml
$dq = '"' # the double quote character
$xsc.Text = "<schema xmlns=" + $dq + "http://www.w3.org/2001/XMLSchema" + $dq + `
"  xmlns:ns=" + $dq + "http://ns" + $dq + "><element name=" + $dq + "e" + $dq +`
 " type=" + $dq + "dateTime" + $dq + "/></schema>"
 
#Create the XML schema collection on the instance of SQL Server.
$xsc.Create()

Community Additions

ADD
Show:
© 2015 Microsoft