Querying through XML String

29 Jun 2006 9:45 AM

Baren

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

30 Jun 2006 1:19 AM

Robbe Morris [C# MVP]

This is the syntax you are looking for I believe:

http://www.eggheadcafe.com/articles/20030627c.asp

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

Show quote

"Baren" <Ba***@discussions.microsoft.com> wrote in message
news:4525F6E1-CF81-4FA4-AFA6-10821BB4325A@microsoft.com...
> 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

30 Jun 2006 4:58 AM

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

Show quote

"Robbe Morris [C# MVP]" wrote:

> This is the syntax you are looking for I believe:
>
> http://www.eggheadcafe.com/articles/20030627c.asp
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
>
>
>
> "Baren" <Ba***@discussions.microsoft.com> wrote in message
> news:4525F6E1-CF81-4FA4-AFA6-10821BB4325A@microsoft.com...
> > 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
>
>
>

1 Jul 2006 12:52 AM

Robbe Morris [C# MVP]

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

Show quote

"Baren" <Ba***@discussions.microsoft.com> wrote in message
news:10D7F60F-897E-4C08-8FC4-6AA2F4AFE38E@microsoft.com...
> 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 [C# MVP]" wrote:
>
>> This is the syntax you are looking for I believe:
>>
>> http://www.eggheadcafe.com/articles/20030627c.asp
>>
>> --
>> Robbe Morris - 2004-2006 Microsoft MVP C#
>> Earn money answering .NET questions
>> http://www.eggheadcafe.com/forums/merit.asp
>>
>>
>>
>>
>>
>> "Baren" <Ba***@discussions.microsoft.com> wrote in message
>> news:4525F6E1-CF81-4FA4-AFA6-10821BB4325A@microsoft.com...
>> > 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
>>
>>
>>