SQL - XML Variable, XML Schema Collection Example

SQL - XML Variable, XML Schema Collection Example

A simple example of using XML variable, XML Schema Collection in SQL

Create a table - Item

CREATE TABLE [dbo].[Item](
 [ID] [int] NOT NULL,
 [CategoryName] [varchar](35) NULL,
 [Amount] [decimal](10, 2) NULL,
 [Detail] [varchar](100) NULL
) ON [PRIMARY]

To import the schema components into the database, you use the CREATE XML SCHEMA COLLECTION statement.
In order to see this ItemSchema created below, go to Object Explorer -> Databases ->[YOUR DATABASE]->Programmability -> Types-> XML Schema Collections

Inserting the records into the Item table
--Declaring an XML variable with XML Schema
DECLARE @ItemList xml(ItemSchema)
--Initializing a variable

--Inserting XML data (from ItemList) into table
INSERT Item
( [ID] ,[CategoryName] ,[Amount] ,[Detail] )

SELECT
node.n.value('(@ID)[1]', 'int') AS ID,
node.n.value('(@CategoryName)[1]', 'varchar(20)') AS CategoryName,
node.n.value('(@Amount)[1]', 'decimal(8,2)') AS Amount,
node.n.value('(@Detail)[1]', 'varchar(1000)') AS Detail
FROM @ItemList.nodes('/main/Item') node(n) --Picking all items (under main node in ItemList) one at a time
GO

Using SELECT to view the contents of the table
Select * From Item