Geek asks and answers

SQL Assemblies vs Application Code for Complex Queries in Large XML Columns

I have a table with several relational columns and one XML column, which sometimes contains a rather large piece of data. I also have a simple web service that uses a database. I need to be able to report things like all instances of a particular element in an XML column, a list of all the individual values ​​for a particular element, such things.

I managed to get a list of all the individual values ​​for the element, but did not get much more than that. I ended up writing incredibly complex T-SQL code to do something that seems pretty simple in C #: go through all the lines in this table and apply this (XPath | XQuery | XSLT) to the XML column. I can filter on relational columns to reduce the amount of data, but for some of these queries there is still a lot of data.

My plan was to embed the assembly in SQL Server (I use 2008 SP2) and create an on-the-fly indexed view for this query (I would have different logic to clear this view). This would allow me to reduce network traffic and, possibly, also allow me to use tools such as Excel and MSRS reports as a cheap user interface, but I see a lot of people saying "just use application logic, not SQL assembly," (I could bark completely on the wrong tree here, I think).

Grabbing a large chunk of data into a web service and doing the processing will also benefit - I am less limited by the SQL Server environment (since I don’t live inside), and my setup process is simpler, but that means I collect a lot of data over the network, saving them in memory, and I process them and then discard some of them.

Any advice here would be appreciated.

thank

Edit:

Thanks guys, you all really helped. The problem was that we generated a row in the table for the file, and each file could have several results, and we will do this every time we run a specific build task. I wanted to flatten this in a table format.

Each execution of this build job checks thousands of files for multiple attributes, and in some cases, each of these tests generates thousands of results (MSIVAL tests were the worst culprit).

(duh!) , , ! , , XML - . , , . , , - > 200 000 < 5 , 3 () , 10 30 XML (- ) .

, , , .

! +1

+3
2

XML TSQL. (http://msdn.microsoft.com/en-us/library/ms189075.aspx). , xml . SQLCLR , , , , .

+2

, , , . -, ? , , , , -. XML - SQL Server, , . , , XML . -, , . , . .

, ? - , , XML, . , , XML , .

, - , . -, , , LINQ to XML XML, XQuery, T-SQL. , , , , T-SQL , XML . . , , - , .

+1

More articles:


All Articles