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 TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *