Browse DevX
Sign up for e-mail newsletters from DevX


Kiss the Middle-tier Goodbye with SQL Server Yukon : Page 2

Yukon's new XML support features cut out the middleman and allow you to deal with your data directly on the database tier. Learn how to use these features to improve your database app's performance and design.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Typed or Untyped
What's the difference bwteen storing data in typed or untyped form? First of all, XML data is stored by default in untyped form—without the use of an XML schema. This XML data must be well-formed. To store XML data in a typed form, you must reference an XML schema within the declaration of your columns, parameters, and variables. The following code shows how to use an untyped XML datatype:

-- Creating a new XML datatype variable DECLARE @xml -- Creating a new table with a xml datatype column CREATE TABLE MyTable(ID int primary key, MyXMLColumn xml) -- Creating a stored procedure with an xml datatype -- parameter CREATE PROCEDURE MyStoredProcedure(ID int, MyXMLValue xml) AS ... -- Creating a new table with a xml datatype column -- with a default value CREATE TABLE MyTable(ID int primary key, MyXMLColumn xml default '<Customer></Customer>")

To use a typed XML datatype, reference an XML schema during the declaration of the datatype. You must register the XML schema inside of Yukon in order to reference it. During this registration process, the XML schema is analyzed and all found types are imported and registered in Yukon's metasystem. Apparently, XML schemas are not simply stored as text blobs in Yukon. Because of this, it's impossible to restore the XML schema to its original state.

Luckily, Yukon provides a function to recreate the XML schema from the metadata. However, keep in mind that annotations, like comments, can't be reproduced because they are not stored in the metasystem. To register a XML schema within Yukon, use the new T-SQL statement CREATE XMLSCHEMA:

CREATE XMLSCHEMA '<?xml-version="1.0" encoding="utf-8" ?> <xs:schema targetNamespace= "http://www.csharp.at/Conferences/2004/02/ASPKonferenz" elementFormDefault="qualified" xmlns"http://www.csharp.at/Conferences/ 2004/02/ASPKonferenz" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:NS="http://www.csharp.at/Conferences/ 2004/02/ASPKonferenz"> <xs:element name="Customer"> <xs:complexType> <xs:attribute name="CustomerID" type="xs:string" use="required" /> <xs:attribute name="State" type="xs:string" use="optional" /> <xs:attribute name="Type" type="xs:string" use="optional" >/ </xs:complexType> </xs:element> </xs:schema>'

After its registered, the XML schema can then be referenced through the target namespace http://www.csharp.at/Conferences/2004/02/ASPKonferenz.

For the new XML schema, a new record is inserted into the system table sys.xml_namespaces. This new record consists of the namespace name and a unique ID assigned to that namespace. With this ID, relations to other system tables are established. For each new type within the XML schema, a new record in the system table sys.xml_components is inserted. In the example, a new record was created for the <Customer> element and for each attribute as well.

With the system function xml_schema_namespace, Yukon allows you to regenerate the XML schema out of the stored metadata. You can call this function from a SELECT statement. As a parameter, you pass in the namespace name of the registered XML schema that should be regenerated:

SELECT xml_schema_namespace (N'http://www.csharp.at/Conferences/2004/02/ASPKonferenz)

To delete an already registered XML schema, use the T-SQL statement DROP XMLSCHEMA. Remember, the deleted XML schema must have no references to other objects (like tables or stored procedures):

DROP XMLSCHEMA NAMESPACE 'http://www.csharp.at/Conferences/2004/02/ASPKonferenz'

Here's how to create a typed XML datatype column within a table:

CREATE TABLE Customers ( ID int primary key, CustomerDocument xml ('http://www.csharp.at/Conferences/ 2004/02/ASPKonferenz') )

As you can see, the target namespace of the referencing XML schema must be provided during the creation of the new table.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date