September 10, 2009 at 11:47 am
is there a way to send xml strings as queries against tables which are plain old row-column type data?
I am researching and all i can find is where the data being queried is xml
Thats not what I want
I want the query itself to be xml, and the results to be a normal, non xml result set.
September 10, 2009 at 12:31 pm
I'm sorry to be blunt (or unpleasant) but I can't see one reason to do this. Can you explain why you would want to do this?
Sorry..
CEWII
September 10, 2009 at 1:17 pm
Once you start explaining why you need to do it would you please support your explanation with some sample data to make it more understandable and -maybe- to give us something to play around with - there might be a different way to achieve the same goal.
September 10, 2009 at 2:04 pm
Thanks for the replies;
The reason to do this is, we are running a business service over ms sql 2008
The user interface, or the client app consuming the biz service, will be making queries for various types of data, most of which will be user supplied fields, so we cant pre write our queries, since the user fields dont exist until the customer created them.
What we have done historically is roll our own data structure containing the fields, and criteria values, logical operations, so on, and in code generate a dynamic sql statement for the query.
I was hoping that there was an xml format which would supply the query, and by using it i would gain an entry point for other consumers, such as web services, etc. I might also gain a standard way to do a more complex search such as where (fielda = 'aaa') and (fieldb='bbb' or fieldc='ccc), that sort of thing. Not that we cant do it in code, I was just fantasizing someone else had already done it in a standard way.
I dont want the query results in xml, because i might have to tweak them before returning them to the caller.
September 10, 2009 at 2:41 pm
Ok, lets see..
You have user supplied fields on a table with a known structure?
I see this as a query with lots of optional parameters, here is some code to demonstrate
IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'TestOptionalQuery' AND schema_id = 1 )
BEGIN
DROP PROCEDURE dbo.TestOptionalQuery
END
GO
CREATE PROCEDURE dbo.TestOptionalQuery
@Name nvarchar(128) = NULL,
@Enabled tinyint = NULL,
@Description nvarchar(512) = NULL
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SELECT *
FROM msdb.dbo.sysjobs j
WHERE ( @Name IS NULL OR j.name = @Name )
AND ( @Enabled IS NULL OR j.enabled = @Enabled )
AND ( @Description IS NULL OR j.description = @Description )
END
GO
You can pass any or no parameters and it will handle the optionals also, this approach is less likely to be hit with SQL injection..
CEWII
September 10, 2009 at 10:43 pm
wjmsdn (9/10/2009)
I was hoping that there was an xml format which would supply the query, and by using it i would gain an entry point for other consumers, such as web services, etc.I dont want the query results in xml, because i might have to tweak them before returning them to the caller.
Well. You could use Native XML Web Services (new in 2005, deprecated in 2008 :doze:) but the current trend seems to be to use WCF or a middle-tier based around ASP.NET
I'm not sure why you object to receiving results as XML, since these are just as easy to work with (in a DataSet, for example).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply