Retrieving Data from XML using T-SQL in SQL Server 2005

It’s quite simple to retrieve data from XML using T-SQL in SQL Server 2005. You can retrieve data using selection criteria as well.



Simple SELECT :

SELECT
Customer.Details.value(‘Name[1]/FirstName[1]’,’VARCHAR(20)’)AS FirstName,
Customer.Details.value(‘Name[1]/LastName[1]’,’VARCHAR(20)’)AS LastName,
Customer.Details.value(‘Address[1]/City[1]’,’VARCHAR(20)’)AS City
FROM @XMLString.nodes(‘//Customer’) Customer(Details)

Output :

SELECT with Criteria :

DECLARE @CityParameter VARCHAR(50)
SET @CityParameter =’Kurigram’
SELECT
Customer.Details.value(‘Name[1]/FirstName[1]’,’VARCHAR(20)’)AS FirstName,
Customer.Details.value(‘Name[1]/LastName[1]’,’VARCHAR(20)’)AS LastName,
Customer.Details.value(‘Address[1]/City[1]’,’VARCHAR(20)’)AS City
FROM @XMLString.nodes(‘//Customer’) Customer(Details)
WHERE Customer.Details.exist(‘Address/City/text()[.= sql:variable(“@CityParameter”)] ‘)= 1

Output :

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: