T-SQL and XML Data Query

Hi Folks,

I am going to show how to query data from a SQL Table based on values in an XML Data Column.

Here is the SQL Script:

Use TestXml
go

 

USE [TestXML]
GO
/****** Object:  Table [dbo].[XmlTest]    Script Date: 12/26/2008 22:44:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlTest]’) AND type in (N’U’))
DROP TABLE [dbo].[XmlTest]
GO
/****** Object:  Table [dbo].[XmlTest]    Script Date: 12/26/2008 22:44:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlTest]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[XmlTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [XMLData] [xml] NULL,
    [Country] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO

insert into Xmltest
(
XMLData,
Country
)
values
(‘<Regions  xmlns="http://schemas.romiko.com/Regions.xsd">
    <Natal Capital="Pietermaritzburg">Surfing</Natal>
    <EasternCape Capital="Bhisho">Hiking</EasternCape>
    <WesterCape Capital="Cape Town">Fishing</WesterCape>
    <Gauteng Capital="Johannesburg">Partying</Gauteng>
    <Mpumalanga  Capital="Nelspruit">Adventure</Mpumalanga>
    <Limpopo  Capital="Polokwane">Safari</Limpopo>
    <NorthernCape Capital="Kimberley">Mining</NorthernCape>
    <FreeState Capital="Bloemfontein">Hiking</FreeState>
</Regions>’,’South Africa’)

Go

WITH XMLNAMESPACES(DEFAULT ‘http://schemas.romiko.com/Regions.xsd’ )
SELECT XMLData.value(‘(/Regions/Natal)[1]’, ‘VARCHAR(30)’) AS Activities   
, XMLData.value(‘(/Regions/Natal/@Capital)[1]’, ‘VARCHAR(30)’) AS CapitalCity
, XMLData.value(‘(/Regions/Gauteng)[1]’, ‘VARCHAR(30)’) AS Activities
, XMLData.value(‘(/Regions/Gauteng/@Capital)[1]’, ‘VARCHAR(30)’) AS CapitalCity
FROM dbo.XmlTest

go

truncate table XmlTest

As we can see, we always start off with the XML NameSpace and then where use the .value notation on the XML Data column name to run the Xpath on. Notice I queries both an element and attribute, where attributes are prefixed with the ‘@’ sign.

Results:

Activites                      CapitalCity                    Activites                      CapitalCity
—————————— —————————— —————————— ——————————
Surfing                        Pietermaritzburg               Partying                       Johannesburg

Hope this gets you started with querying XML in SQL, it is relatively straight forward. I could not think of any exciting XML Data, so South Africa provinces did the trick, some geography lessons for free 🙂

Advertisements
Uncategorized

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s