March 2, 2011 at 3:12 am
Hi - we have a web-based reporting system for our clients to retrieve their data. It is currently quite simple and displays only tabular data that is fetched from the database by executing a stored procedure, one for each report type we provide. The sprocs typically take parameters to specify client, date range, etc., and return the columns appropriate to that report. The web stuff then uses the column names returned in the query to create a table for display. Oh, and to aid things like sorting and filtering we encode the data type in a 'tag' in the column name, which then gets stripped out at page build time. All fine and works well enough.
What we want to do now is decouple the web server from the report data source to avoid cross-server queries, and instead implement an XML-based remote service using Service Broker.
My problem is this: how do I phrase my select query to produce the xml that contains both the schema and the data for any given result set, such that at the far end the schema part can be used to extract the data rows. This has to work for any arbitrary results set - there are some reports where the columns returned can vary depending on client activity, so we cannot work from any pre-determined schema for any of the reports.
I have tried to do this within T-SQL using FOR XML with the XMLSCHEMA keyword, and it produces a whole xsd:schema for the result set, but I can't find how to then tell SQL to use the generated XML to correctly recreate the result set in, for example, a local table. I am assuming there must be a way, otherwise it seems pointless to create the xml in the first place.
If anyone has any ideas, pointers, suggestions, etc. I'd be delighted to hear them 😉
March 2, 2011 at 11:43 am
I don't have a concrete solution for you but I do have some random thoughts on the matter. If it sparks additional conversation or evokes a solution from another, then great, I think that's why we're here 🙂
but I can't find how to then tell SQL to use the generated XML to correctly recreate the result set in, for example, a local table. I am assuming there must be a way, otherwise it seems pointless to create the xml in the first place.
XML being hierarchical I don't see how there could be a function capable of converting any XML document to a tabular result set. The shape of the resulting tabular set would depend on the structure of the XML and a conversion may not even be feasible. Conversely it is always possible to model a tabular resultset as an XML document with a single level in the hierarchy which speaks to why FOR XML always makes sense.
I think of FOR XML to accomplish these useful things:
1. get tabular data into an XML format so it can be consumed by a data client that prefers processing XML over tabular resultsets
2. going beyond relational by generating hierarchical XML documents using nested FOR XML queries so a singular entity, namely an XML document, can contain related data that can be broken down and stored in a hierarchical model in another data store
DECLARE @xml XML;
SET @xml = (select * from INFORMATION_SCHEMA.tables for xml RAW('datarow'), ROOT('results'), XMLSCHEMA)
SELECT @xml AS xml_doc
It will allow you to transport to another SQL Server a singular entity containing your schema and data. In a T-SQL context you can then select the xsd:schema node into an xml variable using xquery so you can inspect it and use its details to generate a table definition (all dynamic code, not going to be pretty). You can iterate over your datarows to get your data into your new table (also all dynamic code, also not going to be pretty). I think it would be a lot of not-pretty, dynamic code involving xquery. This type of work would be much easier to implement in an application language with string XML support (like C#) however it could all be done in T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 2, 2011 at 11:57 am
Thank you for your thoughts. I agree that something could be written to parse the output from your query (which is much the same as I had been experimenting with), but it still begs the question - "What is the XMLSCHEMA keyword actually for?". OK, it "bundles" the schema for the returned data set along with the data, but what is the point of that if there is no equivalent "unbundler"?
I'm sure I'm missing something fundamental here and that there really is a ready-made process that just consumes such xml and allows you to query the data contained therein, but I can't find how to do it.
In essence, what I need is something that does "select * from <xml containing data and schema>" and returns the columns with their original names and data types.
I may be asking too much, but there again ... refer to first para.:ermm:
Bob
March 2, 2011 at 1:45 pm
I hope I am missing something and someone shows me the way on this one, it is why I jumped onto this thread, but from all my reading and experience the FOR XML use cases define outputting tabular data as hierarchical XML so they can be consumed in a non-T-SQL application domain/context.
I am seeing a fair bit of info about creating tables from an XSD but not within a T-SQL context:
SQLXMLBulkload: http://blogs.msdn.com/b/monicafrintu/archive/2007/06/12/how-to-create-tables-in-a-database-using-an-xsd-schema-and-sqlxmlbulkload.aspx
More SQLXML: http://dbaspot.com/forums/sqlserver-programming/213058-xsd-sql-server-tables.html
My problem is this: how do I phrase my select query to produce the xml that contains both the schema and the data for any given result set, such that at the far end the schema part can be used to extract the data rows. This has to work for any arbitrary results set - there are some reports where the columns returned can vary depending on client activity, so we cannot work from any pre-determined schema for any of the reports.
What does the underlined point say about your architecture? Can you detail the services and the details of their participation in the data flow?
e.g.
1. Service Broker does what in your design? It accepts what parameters and delivers what data in what format?
2. What role does T-SQL play in the delivery of the data?
3. What is presenting your data to the end user and what data format is it expecting to see, tabular or could it be made to process xml?
etc.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 2:06 am
Sorry for the delay in responding. Hectic week last week, but back with the program now...
OK. Perhaps my question was badly worded. I know how to get schema and data into xml from a query (in one format at least), but I don't know how to then re-query the xml to recreate the original recordset.
The way Service Broker fits in to all this is quite simple - we are re-architecting our systems to make them service-based, and to avoid any direct cross-server querying. Whereas now the web server would execute a stored procedure on the reporting server to retrieve a recordset that represents the tablular data for the report, we now want the reporting server to receive its requests over a SB queue, and return the results asychronously for eventual display to the user. As XML is the most ubiquitous means of transferring structured data over SB queues, that is what we have chosen to do.
The web server currently has its own local SQL Server instance; it is currently querying a local table to find what query to run to produce the report that the client has requested. This query is then executed on the remote server where the stats data is held. The process then blocks until the recordset is returned. The page builder then creates a table containing whatever columns are returned and displays the data.
When reports take a while run (sometimes a couple of minutes or more) the web pages are no longer responsive, and this has led to user confusion and a range of other issues. We want to provide a decoupled system that sends off the request, and then separately responds to the incoming report data by opening another window asynchronously to display the results. A message-based system seems appropriate for this, and we are familiar with SB as we use it all over the place for other purposes.
So, back at the problem - when the xml arrives defining both the schema and the data, how do we query that such that the resultant recordset looks the same as the recordset returned by the stored procedure we call now?
March 7, 2011 at 10:20 am
I Googled "xml to tabular resultset using schema" and our ongoing thread is the 8th result 🙂
Thanks for your comments, I can better understand your problem domain now, but I think your architecture is unlike others in that SQL Server is not only the producer of the XML but also the consumer of the XML resultset. In other application settings where I have made use of FOR XML a .NET or Java object better suited to handling XML resultsets was the consumer...not another SQL Server stored procedure. Converting the XML to a memory-resident data structure like a .NET DataTable or DataSet for later binding to a grid on a report or form is trivial. The fact that you'll have Service Broker in between the two T-SQL contexts now seems immaterial, although for me it is an interesting detail only because I am trying to learn more about SB use cases.
I am not sure I helped you much, but I think we sufficiently deconstructed your problem domain so that someone with more experience in this area can easily swoop in with a great solution for you. I hope that's what happens...sorry I could not be of more assistance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2011 at 9:48 am
I thought of an option...I haven't done a proof-of-concept on it because I don't have a copy of Visual Studio here that supports SQL Server Database Projects but I did research each of the steps below and it appears to be a viable option in terms of functionality. That said, performance should be considered heavily given all the context switching and your potential for pushing large XML documents representing large resultsets through...here goes...
You could create a SQLCLR stored procedure that:
1. accepted your XML schema and data as parameter(s)
2. derived an ADO.NET DataTable from your schema (standard ADO.NET can do this)
3. loaded the XML data into the DataTable (standard ADO.NET can do this)
4. output the contents of your DataTable to a tabular resultset (will take some trivial but tedious work building up SqlMetaData to define shape of resultset)
I may try this out just to see if the theory matches up to reality...I'll let you know if I do...please do the same if you take a shot at it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2011 at 3:31 pm
Hi, FYI, I did something similar to this using a SQL CLR stored procedure, it actually works pretty well! Doesn't handle the "ROOT" option yet, but should work for most things:
http://architectshack.com/ClrXmlShredder.ashx
It can work with or without Schema, but in general I'd recommend always including it (so that binary values can be decoded automatically and so that Nulls never cause issues).
I realize it's waaay late for the OP, but I'd welcome any comments if anyone else finds this useful/interesting.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
June 20, 2011 at 6:31 am
Tao Klerks (6/19/2011)
Hi, FYI, I did something similar to this using a SQL CLR stored procedure, it actually works pretty well! Doesn't handle the "ROOT" option yet, but should work for most things:http://architectshack.com/ClrXmlShredder.ashx
It can work with or without Schema, but in general I'd recommend always including it (so that binary values can be decoded automatically and so that Nulls never cause issues).
I realize it's waaay late for the OP, but I'd welcome any comments if anyone else finds this useful/interesting.
:Wow: Too cool! I will definitely have a look into the code when I get a chance. Thanks for sharing!
Not sure if Bob has settled on a solution but we had a couple email messages after this thread and last I heard he was working on a 100% T-SQL solution. You still out there Bob?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 6:43 am
Yes, still here, but I've had my head firmly in other issues lately. The code for the CLR sproc looks interesting - I'll give that a try as soon as I can, and see how it addresses our problem.
It is still odd, I think, that you need to go to these lengths to reconstruct a recordset, given that you can output xml data and schema in T-SQL !!
June 20, 2011 at 6:57 am
Bob, to be fair, the CLR stored proc that I linked is much more complicated than it needs to be for your purposes - I wanted to support a variety of FOR XML option combinations, rather than forcing a specific set of options (although there are some combinations that simply won't ever work, like omitting the schema and omitting the XSINIL parameter and having a null in the first row/result).
On the plus side, I did a performance test after posting this yesterday, the proc churned through a 25MB Xml variable in under 20 seconds (25,000 rows of a wide table) - so at least performance isn't too much of a concern.
I'd love to hear how it goes!
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
June 20, 2011 at 7:14 am
Tao -
That's great, thanks. If only I can get rid of all the other stuff that is more important than everything else, then I'll be able to get to play with it.;-)
I'll let you know. May be a while though....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply