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