-
reading xml in a sql table
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
-
Look for OPENXML in books online, it has an example of how you can prepare a xml document and select from it
-
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
-
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
-
If you're going to this frequently for differnet fields, this would an ideal candidate for a user-defined function.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|