Querying through XML String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

Throught code I will be generating a XMl like this "<ROOT><Employee
EmpID="137904" Name=""/></ROOT>". Now using this XML string as a parameter to
a store procedure, I want to retrieve the data matching the XML attributes.

Can anyone help me out in this regards

Baren
 
Hi! Robbe,

Thanks for your reply. I got something interesting. I am using SQL Server
2005 and it has a data type called 'XML'. We can use that to query the
attribute values. here is an example

DECLARE @myDoc xml

DECLARE @EmpID int

DECLARE @EmpName VARCHAR(50)

SET @myDoc = '<ROOT><Employee EmpID="1234" EmpName="John"/></ROOT>'



SET @EmpID = @myDoc.value('(/ROOT/Employee/@EmpID)[1]', 'int' )

SET @EmpName = @myDoc.value('(/ROOT/Employee/@EmpName)[1]', 'VARCHAR(50)' )

SELECT * FROM Employee WHERE EmpID=@EmpID AND EmpName=@EmpName


Regards,
Baren
 
You didn't say you had 2005...

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp





Baren said:
Hi! Robbe,

Thanks for your reply. I got something interesting. I am using SQL Server
2005 and it has a data type called 'XML'. We can use that to query the
attribute values. here is an example

DECLARE @myDoc xml

DECLARE @EmpID int

DECLARE @EmpName VARCHAR(50)

SET @myDoc = '<ROOT><Employee EmpID="1234" EmpName="John"/></ROOT>'



SET @EmpID = @myDoc.value('(/ROOT/Employee/@EmpID)[1]', 'int' )

SET @EmpName = @myDoc.value('(/ROOT/Employee/@EmpName)[1]',
'VARCHAR(50)' )

SELECT * FROM Employee WHERE EmpID=@EmpID AND EmpName=@EmpName


Regards,
Baren

Robbe Morris said:
This is the syntax you are looking for I believe:

http://www.eggheadcafe.com/articles/20030627c.asp
Click to expand...
Click to expand...
 
Back
Top