Create procedure [dbo].[GetPersonDetails]
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
end