XML is one of the data types in SQL Server 2005.It is considered as one of the significant changes introduced in SQL Server 2005.In the previous versions, storing and retrieving XML data were possible but it was not as easy and extensible as it is in the current version. The XML data type lets the user to store XML data in the SQL Server database. In this article we will be discussing how to store, retrieve and query XML data.
Support for XML is integrated into all the components of SQL Server 2005.The SQL Server 2005 supports XML in the following way:
- SQL Server 2005 supports XML data type.
- The ability to specify an XQuery query against XML data stored in columns and variables of the xml type.
- Enhancements to OPENROWSET to allow bulk loading of XML data.
- Enhancements to the FOR XML clause and OPENXML function introduced in SQL Server 2000.
Let’s now take a look at an example. This example demonstrates how to create a table that contains an XML column. We will also insert some records into the table.
1. Create table with XML column
Create table dbo.Student(StudentID int,StudentName varchar(50),Studentcontactdetails XML)
2. Insert valid XML data into the table:
Insert into dbo.Student values(1,’ABC’,'<ROOT><Student>123,XYZ street,London</Student></ROOT>’)
If we try to insert invalid XML, it will result in an error message.
Insert into dbo.Student values(1,’ABC’,'<ROOT><Student>123,XYZ street,London’)
Msg 9400, Level 16, State 1, Line 1
XML parsing: line 1, character 36, unexpected end of input
We can also create variables of XML type.
Declare @x xml
Typed vs. Untyped XML
Untyped XML can be stored in any form. The XML should be a well formed one. When the user inserts a value to the XML column, a check will occur to see whether the data that is about to be inserted matches the XML standard. The value is not validated against any XML schema.Untyped xml provides a more flexible way to store data.
Typed XML is used when the user wants to validate the XML with an XML schema. The XML schema has to be mentioned when creating the XML datatype.This is done by referring to the XML schema.XML schema has to be initially stored and catalogued in the database. Thus the XML that is validated against a XML schema is Typed XML.The typed XML is declared in the following way:
Declare @x XML(schema.xmlschemacollection)
The typed XML is suitable in scenarios where the XML data which is stored in the database has to match a strict definition such as, for example, an invoice.
The XML schema can be created as follows:
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
- relational_schema:-Identifies the relational schema name. If not specified, default relational schema is assumed.
- sql_identifier :-Is the SQL identifier for the XML schema collection
- Expression:-Is a string constant or scalar variable. It can be varchar, varbinary, nvarchar, nvarbinary, or xml type.
Example:
CREATE XML SCHEMA COLLECTION Chemicals AS ‘
<schema xmlns=”http://www.w3.org/2001/XMLSchema”>
<element name=”root”>
<complexType>
<sequence>
<element name=”ChemicalName” type=”string”/>
<element name=”Symbol” type=”string”/>
</sequence>
</complexType>
</element>
</schema>’
After the XML schema is catalogued, the information about it can be obtained by using the “XML_schema_namespace” function.
Example:
SELECT xml_schema_namespace(N’dbo’,N’Chemicals’)
Once the XML schema is defined, we can use it and refer to it in CREATE TABLE or DECLARE XML statement.
Example:
1)
Declare @xml xml(Chemicals)
set @xml='<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>’
2)
Insert into students (studentname,Labid,Experimentid,Experimentchemical)
values (‘ABC’,1,1,'<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>’)
Querying XML Data
The methods which can be used to retrieve data from an xml datatype are as follows:
1) Query method: This method returns a fragment of untyped XML.The following example shows how to return a value from within an XML column.
Example:
select experimentchemical.query(‘/root/ChemicalName’) from students
Result:
This will result in a partial result set but will return a fragment of untyped XML.
<ChemicalName>Sulphuric Acid</ChemicalName>
To retrieve only the values,we can use the data function.
2) Value Method:The value method is similar to the query method.The only difference is that the value method will accept an additional parameter to determine the resulting scalar datatype.
Example:
If the user want to see the second customer’s customer ID then the query would be as follows:
select @x.value(‘(/root/CustomerDescription/@CustID)[2]’,’int’)