473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query out an xml element from a field containing xml data

'message' Field Data:
<xml>
....
<test>ABC</test>
....
</xml>

How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'

Jan 26 '06 #1
3 8614
What you could do, is write a user defined function. Something like

create function [dbo].[fn_GetText]
(
@Text as XML
)
returns varchar(50)
begin
declare @Result as varchar(50)
declare @Count as int
declare @Start as int

set @Count = 1
set @Start = 0

while @Count <= len(@Text)
begin
if substring(@Text, @Count, 6) = '<test>'
set @Start = @Count + 6

if substring(@Text, @Count, 7) = '<\test>' and @Start <> 0
set @Result = substring(@Text, @Start, @Count - @Start - 1)

set @Count = @Count + 1
end

return @Result
end

go

Jan 26 '06 #2
Hi Mike,

thanks for the reply.

As my comfort level is much higher with C#, I just wrote some string
parsing code and got my result :)

Next time I need to spin off a UDF, I will remember this.

Ranjith

Jan 26 '06 #3
Ranjith (ra**************@hotmail.com) writes:
'message' Field Data:
<xml>
...
<test>ABC</test>
...
</xml>

How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'


create table Persons (message xml NOT NULL)
go
insert Persons (message) VALUES('<xml><test>ABC</test></xml>')
go
select message.value(N'(/xml/test)[1]', 'varchar(10)')
from Persons
go
Drop table Persons

This is for SQL 2005. You did not mention which server of SQL Server
you are using. This matters a lot when XML is involved, as the XML
support in SQL 2005 is greatly extended over what is in SQL 2000,
including a new query language, XQuery which I'm using above.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Stu | last post by:
I have the following code which I am having difficulty getting to work. I think it may be a problem with the $got query that is being run as if I set that to a set value then my site seems to run...
20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
4
by: Christopher | last post by:
This should be a quick one. URL: http://cfa-www.harvard.edu/~cpilman/Stuff/flush.html Code: ============================= <!DOCTYPE HTML Public "-//W3C//DTD HTML 4.01//EN">...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
4
by: Martin Lacoste | last post by:
(Access 2000) Two issues: Within a query, I need to return a field name as data (see eg. below). I need to search within 80 fields (same criteria) - is there a way to avoid 80 separate...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
7
by: Cruisemate | last post by:
I have a table with numerous fields including timeIn and timeOut field. I need to find out how many people were clocked in during each operating hour. Can I run a query that will allow me to...
4
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.