Using the XML data type, we can store XML in its native format, query the data within the XML, efficiently and easily modify data within the XML without having to replace the entire contents, and index the data in the XML.
How to use XML data Type - We can use XML as any of the following:
1) A variable
2) A parameter in a stored procedure or a user-defined function (UDF)
3) A return value from a UDF
4) A column in a table
Limitations of an XML data type
1) A variable
2) A parameter in a stored procedure or a user-defined function (UDF)
3) A return value from a UDF
4) A column in a table
Limitations of an XML data type
You should keep in mind that there are some limitations of XML data type. Apart of these limitations, XML data type can contain and be checked for null values, unlike other native types.
For example: directly, we can never compare one instance of an XML data type to another instance of an XML data type. If we want to compare them then we need to convert that instances to a text data types.
Another limitations of XML data type that we can not Group BY or Order BY clause with an XML Data Type.
A few other Limitations of XML data type —in this case, when it is used as a column in a table:
1) It cannot be used as a primary key or foreign key.
2) It cannot be declared with a UNIQUE constraint or COLLATE keyword.
Strongest Points
But these limitations don’t really affect the XML data type when it is used appropriately. The XML data type also has a rich feature set that more than compensates for these limitations.
XML Data Type as a Variable-
Simply we need to declare a variable for XML data type and set the XML value in generic piece of XML as shown below:
Working with XML in Tables
To create an XML object in your database, you simply specify the XML data type as you would any other type. For instance, the following Transact-SQL code creates the Resumes tables, inserts data into the table, and then retrieves data from that table:
SQL Server Schema Collections
SQL Server provides a simple way to us, How can we create your own schemas and store them in the database as database objects, and to then enforce a schema on any XML instance, including columns in tables and SQL Server variables.
Example -
For example: directly, we can never compare one instance of an XML data type to another instance of an XML data type. If we want to compare them then we need to convert that instances to a text data types.
Another limitations of XML data type that we can not Group BY or Order BY clause with an XML Data Type.
A few other Limitations of XML data type —in this case, when it is used as a column in a table:
1) It cannot be used as a primary key or foreign key.
2) It cannot be declared with a UNIQUE constraint or COLLATE keyword.
Strongest Points
But these limitations don’t really affect the XML data type when it is used appropriately. The XML data type also has a rich feature set that more than compensates for these limitations.
XML Data Type as a Variable-
Simply we need to declare a variable for XML data type and set the XML value in generic piece of XML as shown below:
---- declare
variable with xml data type
DECLARE @XmlData
AS xml
---- set the value
in xml variable in a generic piece of XML to represent basic order
information
set @XmlData= '
<Orders>
<Order>
<OrderId>12345</OrderId>
<CustomerId>C00125</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-05:00</OrderDate>
<OrderAmount>125.90</OrderAmount>
</Order>
<Order>
<OrderId>12346</OrderId>
<CustomerId>C12098</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-08:00</OrderDate>
<OrderAmount>252.00</OrderAmount>
</Order>
</Orders>'
----- select the
value from the xml variable
SELECT @XmlData
|
Working with XML in Tables
To create an XML object in your database, you simply specify the XML data type as you would any other type. For instance, the following Transact-SQL code creates the Resumes tables, inserts data into the table, and then retrieves data from that table:
------ create table
to store xml data type values
CREATE TABLE
OrdersXML
(
OrdersId int
PRIMARY KEY,
OrdersDoc xml
NOT NULL DEFAULT '<Orders />'
)
---- declare
variable with xml data type
DECLARE @XmlData
AS xml
---- set the value
in xml variable in a generic piece of XML to represent basic order
information
set @XmlData= '
<Orders>
<Order>
<OrderId>12345</OrderId>
<CustomerId>C00125</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-05:00</OrderDate>
<OrderAmount>125.90</OrderAmount>
</Order>
</Orders>'
----- Insert values
into the OrdersXML table
INSERT INTO
OrdersXML (OrdersId, OrdersDoc) VALUES (1, @XmlData)
------ select value
from the table now
select OrdersId, OrdersDoc from OrdersXML
|
SQL Server Schema Collections
SQL Server provides a simple way to us, How can we create your own schemas and store them in the database as database objects, and to then enforce a schema on any XML instance, including columns in tables and SQL Server variables.
Example -
CREATE XML
SCHEMA COLLECTION
OrdersXSD AS '
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType
name="OrderAmountFloat" >
<xsd:restriction
base="xsd:float" >
<xsd:minExclusive
value="1.0" />
<xsd:maxInclusive
value="5000.0" />
</xsd:restriction>
</xsd:simpleType>
<xsd:element
name="Orders">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="Order">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="OrderId" type="xsd:int" />
<xsd:element
name="CustomerId" type="xsd:int" />
<xsd:element
name="OrderDate" type="xsd:dateTime" />
<xsd:element
name="OrderAmount" type="OrderAmountFloat" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
|
SQL - FOR XML Commands
As we are well aware that SQL Server supports an enhancement to the T-SQL syntax that enables normal relational queries to output their result set as XML, using any of these four approaches:
1) FOR XML RAW
2) FOR XML AUTO
3) FOR XML EXPLICIT
4) FOR XML PATH
1) FOR XML RAW
2) FOR XML AUTO
3) FOR XML EXPLICIT
4) FOR XML PATH
No comments:
Post a Comment