reading xml in a sql table

[timc]

I receive in a table a field which is an xml string
like below

<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</NewOrder>

Is there a way read this field with a select statement? I need to strip out the entryid value in a trigger

[skhanal]

Look for OPENXML in books online, it has an example of how you can prepare a xml document and select from it

[Rawhide]

That's not valid XML, so reading it as XML won't work unless you first manipulate the data.

Your sample is missing the closing EntryID tag. Are you sure you typed it in correctly?

Declare @XML varchar(500), @EntryID varchar(100)
Declare @Start int, @End int, @Length int

Set @XML = '<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</NewOrder>'

Set @Start = CharIndex('<EntryID>', @XML) + Len('<EntryID>')
Set @End = CharIndex('</', @XML, @Start)
Set @Length = @End - @Start

Set @EntryID = Substring(@XML, @Start, @Length)

Select @EntryID

[timc]

Thanks, that works great, only had to make small change to below since the data type was text


declare @EntryID varchar(100)
Declare @Start int, @End int, @Length int



select @Start = (CharIndex('<EntryID>', request) + Len('<EntryID>')) from inq
select @End = (CharIndex('</', request, @Start)) from inq
Set @Length = @End - @Start

select @EntryID = (Substring(request, @Start, @Length)) from inq

Select @EntryID

[Rawhide]

If you're going to this frequently for differnet fields, this would an ideal candidate for a user-defined function.