How to search Address as an xml datatype value in a table using a procedure?

Merryj
Posted by Merryj under Sql Server category on | Points: 40 | Views : 2469
Create procedure [dbo].[SearchPersonDetails]
@FirstName varchar(50),
@LastName varchar(50),
@HouseName varchar(30),
@HouseNumber varchar(30),
@City varchar(30),
@PO varchar(30),
@Pin varchar(10),
@State varchar(30)
as
begin
SELECT
Person_Id,
FirstName,
LastName,
Person_Address.value('(/person//HouseName/node())[1]', 'nvarchar(max)') as HouseName,
Person_Address.value('(/person//HouseNumber/node())[1]', 'nvarchar(max)') as HouseNumber,
Person_Address.value('(/person//City/node())[1]', 'nvarchar(max)') as City,
Person_Address.value('(/person//PO/node())[1]', 'nvarchar(max)') as PO,
Person_Address.value('(/person//Pin/node())[1]', 'nvarchar(max)') as Pin,
Person_Address.value('(/person//State/node())[1]', 'nvarchar(max)') as Person_State from tblPerson
where FirstName like '%'+@FirstName+'%' and LastName like '%'+@LastName+'%' and
Person_Address.value('(/person//HouseName)[1]', 'nvarchar(max)') like '%'+@HouseName+'%' and
Person_Address.value('(/person//HouseNumber)[1]', 'nvarchar(max)') like '%'+@HouseNumber+'%' and
Person_Address.value('(/person//City)[1]', 'nvarchar(max)') like '%'+@City+'%' and
Person_Address.value('(/person//PO)[1]', 'nvarchar(max)') like '%'+@PO+'%' and
Person_Address.value('(/person//Pin)[1]', 'nvarchar(max)') like '%'+@Pin+'%' and
Person_Address.value('(/person//State)[1]', 'nvarchar(max)') like '%'+@State+'%'
end

Comments or Responses

Posted by: T.saravanan on: 4/28/2011 Level:Silver | Status: [Member] [MVP] | Points: 10
Hi,

In your procedure where is the XML file.I think you miss that one.Kindly update that one and also post your code inside the code tag.
Posted by: PandianS on: 4/29/2011 Level:Silver | Status: [Member] [MVP] | Points: 10
Hi

Nice Script & sample.

@T.Saravanan : Seems to be the column Person_Address is an XML data type and XML data, so, The script will work fine. :)

Cheers

Login to post response