SQL Server XML Data Type


A really good source of SQL Server information is the website SQL Server Central. If you are interested in learning about XML in SQL Server you can have a look at their Stairway to XML series of articles. The XML data type is discussed in the second level.

The XML data type lets you store XML data in database objects such as variables, columns, and parameters. When you configure one of these objects with the XML data type, you simply specify the type name as you would any other SQL Server type. The XML data type ensures that your XML data is well formed, that is, conforms to ISO standards. You can use the data type to store either XML documents or fragments.

There are some limitations with the XML data type but you probably can live comfortably with them.

  • XPath is a syntax for defining parts of an XML document
  • XPath uses path expressions to navigate in XML documents
  • XPath contains a library of standard functions
  • XPath is a major element in XSLT and in XQuery
  • XPath is a W3C recommendation

The article at SQLServerCentral.com covers these three sections, however I will just show the code for the second one:

  • Defining an XML Variable
  • Defining an XML Column in a Table
  • Defining an XML Parameter in a Stored Procedure

Defining an XML Column in a Table

USE MyDatabase
GO
IF OBJECT_ID('dbo.StoreClients') IS NOT NULL
DROP TABLE dbo.StoreClients
GO
CREATE TABLE dbo.StoreClients
(
StoreID INT IDENTITY PRIMARY KEY,
ClientInfo XML NOT NULL
)
GO
DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
INSERT INTO dbo.StoreClients (ClientInfo)
VALUES(@ClientList)
GO
SELECT TOP (10) [StoreID]
      ,[ClientInfo]
  FROM [XML].[dbo].[StoreClients]

The above script produces the following

Series Navigation<< XML and XPath

Leave a comment

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