XML Datatype in SQL Server- A small example

Dear All,

XML data type was introduced in SQL Server 2005. This type allows you to store XML data up to 2 GB.

Here is a small example of, pretty much self-explanatory.

---create xml data type----
CREATE Database XMLTest
GO
 
USE XMLTest
GO
 
 
 
CREATE TABLE dbo.Invoices 
(InvoiceID int, 
 SalesDate datetime, 
 CustomerID int, 
 ItemList xml) 
 
 
--- implicily cast string values---
 
DECLARE @itemString nvarchar(2000) 
SET @itemString = '<Items> 
                     <Item ProductID="2" Quantity="3"/> 
                     <Item ProductID="4" Quantity="1"/> 
                   </Items>' 
  
DECLARE @itemDoc xml 
SET @itemDoc = @itemString 
  
INSERT INTO dbo.Invoices 
VALUES 
(1, GetDate(), 2, @itemDoc) 
 
-- check the data
	 
select * from dbo.invoices
 
 
-- example 2
--- constant string expression--- 
 
INSERT INTO dbo.Invoices 
VALUES 
(2, GetDate(), 2, '<Items> 
                    <Item ProductID="2" Quantity="3"/> 
                     <Item ProductID="4" Quantity="1"/> 
                   </Items>') 
 
 
-- check the data
 
select * from dbo.invoices

Regards

Rahul Sharma

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook