Tag Info

Hot answers tagged

15

The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is: [Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0) This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar ...


12

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario. The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row ...


11

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely. but the size of the XML can be quite variable (esp given that a binary would pass it to the ...


11

All you need is persistence of your XML. Use a NoSQL solution or the file system. There is no benefit in using an RDBMS, unless you want to use it instead of NoSQL or the file system.


11

The exist() Method (xml Data Type) returns a bit. 1 if at least one node is found and 0 if no nodes are found (empty result set). To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0. [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) ...


10

I bookmarked Phil Factor's blog post Normalisation and 'Anima notitia copia' today as it neatly summarises the case for and against normalising certain types of data. Run the following query on a SQL instance and see if you agree. SELECT * FROM sys.syslanguages SQL enables you to create relational databases. However, even if it smells bad, it is ...


10

There are a lot of facets to this, so it really depends. Does adding the XML typed columns to the same table change the speed of querying that table? More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should ...


8

When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL). The XML is quite complex so without knowing how you want the data ...


8

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML. Using this table: create table T(XMLCol xml not null) insert into T values('<root><item value = "1" /></root>') And this rather simple XML query: select X.N.value('@value', 'int') from T cross apply ...


8

There are two points of confusion here. The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value. The second is that you can use NULL in equality comparisons; and you cannot. Try this: SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END; Result: ---- 0 ...


6

Give this a try: SELECT t1.UserName AS "Account/UserName", t1.Hash AS "Account/Hash", t2.Phone AS "Personal/Phone", t2.City AS "Personal/City" FROM table1 t1 INNER JOIN table2 t2 ON t1.rid = t2.rid FOR XML PATH ('UserDetails'); I have added a second code sample in response to your comment. I have no idea if this will work, ...


6

The second statement is creating an object table. It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...


6

That is the code from my article originally posted here: http://www.sqlservercentral.com/articles/deadlock/65658/ If you read the comments you will find a couple of alternatives that don't have the performance problems that you are experiencing, one using a modification of that original query, and the other using a variable to hold the XML before ...


5

It depends, as XML is not an RDBMS. SQLite allows the querying of relations. XML (and JSON) allow for the navigation of hierarchies. Completely different ontological mappings of reality. If you need an RDBMS backend, SQLite is for you. If you need record storage without any sophisticated query requirements, XML may work, depending on your language.


5

A nullable column takes no space if variable length in SQL Server. The fact of being NULL is stored in the NULL bitmap. You can index it if required with filtered indexes so you ignore NULL columns. Adds complexity when you consider point 1. Don't. Hard to search, parse etc: you will regret this later It also depends on size: will this be char(1000) for a ...


5

Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on. If you require the data to be in a particular order, use the ORDER BY clause.


5

You have not specified the length of @id. Using only varchar will in this case give you a length of one so the content of @id will be x. Your first query checks for nodes that contain x and that matches all rows. The second query checks for nodes that has x as value and no row has that. Change the declaration of @id to DECLARE @id varchar(3) and everything ...


5

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values. SQL Fiddle The extra cost for the second query comes from using a table valued function to shred the XML. First query: Second query:


5

You can use X-Query for that: SQL Fiddle MS SQL Server 2008 Schema Setup: CREATE PROCEDURE dbo.ParseXML @XML XML AS BEGIN SELECT Book.value('@id','NVARCHAR(100)') AS id, Book.value('author[1]','NVARCHAR(100)') AS author, Book.value('title[1]','NVARCHAR(100)') AS title, Book.value('price[1]','DECIMAL(10,4)') AS price, ...


5

You can easily test the performance yourself. Create a regular table that you can test your queries on. create table myTable ( yourXML XML ) Add a couple of rows that will give you a match. INSERT INTO myTable SELECT '<z><a><b>1</b><c>2</c></a></z>' INSERT INTO myTable SELECT ...


5

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end. Here's an example: DECLARE @xml AS XML = '<DATA> <ROW RowNumber="1"> <COLUMN Name="ProdID" Value="1234" /> <COLUMN Name="AppID" Value="20144" /> <COLUMN Name="ConsentDesc" Value="My ...


4

If Queries against the XML will happen by sql server xml capabilities, then use XML type to store a xml to avoid casting And keep in mind, that XML type may be stored little bit slower due to xml validation, but underlying type of XML is ordinary varbinary(max)


4

There is a comparable issue logged on connect for the version you're having a problem with - An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1. Using SQL2008, when shredding XML using the nodes query, the performance is fine when just SELECTing. However, when you want to INSERT the data to a table/temptable/tablevariable the ...


4

You can import your data into a staging table then update the product reference data from that. The product table can hold your 'available' flag. Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.


4

While TVPs would be a better solution, this can be done via XML as well. I talked about it a while back in an article and my blog.


4

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding). This function works for any path, but needs to be created as superuser: create or replace function ...


4

I use sp_OACreate extensively to do the sort of thing you are doing. I suspect that your problem may be that the size of the XML data you are receiving exceeds 8000 characters. (i.e. SQL may not be returning any data when you exceed 8000 characters). sp_OAGetProperty and other extended stored procedures cannot pass varchar(MAX) parameters As a ...


4

You could try using MySQL's mysqldump utility. There is an option to make mysqldump export in XML [redwards@lw-lts-155 ~]$ mysqldump --help | grep xml -X, --xml Dump a database as well formed XML. In your case, you could dump the table mydb.mytable like this: mysqldump --xml mydb mytable > mytable.xml EXAMPLE For the given table ...


4

Given this table: CREATE TABLE IQ ( XMLDT xml NULL ); We first need a scalar function to extract the value of interest from the XML: CREATE FUNCTION dbo.GetRow(@XMLDT AS xml) RETURNS nvarchar(10) WITH SCHEMABINDING AS BEGIN RETURN @XMLDT.value('(./row/text())[1]','nvarchar(10)'); END; Now we can add a computed column that uses the function: ...



Only top voted, non community-wiki answers of a minimum length are eligible