Hot answers tagged

28

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 ...


13

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


13

This is close but missing a few pieces. You extract into rows of TINYINT the decimal value from each <Element> in the XML (e.g. 137, 80, 78, etc), but then the FOR XML PATH('') converts them back into strings and concatenates them, leaving you with a UTF-16 encoded string of "1378078...". Converting that into VARBINARY just turns each string digit -- ...


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

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

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

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


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'')])) ...


11

You can shred the XML and rebuild it again using XQuery. declare @X xml = ' <Root attr1="val1" attr2="val2"> <Elements> <Element Code="1" Value="aaa" ExtraData="extra" /> <Element Code="2" Value="" ExtraData="extra" /> <Element Code="3" ExtraData="extra" /> </Elements> <ExtraData> ...


10

You should shred on /WEBSITES/WEBSITE in your second query as well and use a predicate against the NAME node in the values clause and then get the value from VALUE node. select S.X.value('(WEBSITE_ID/text())[1]', 'nvarchar(100)') as WEBSITE_ID, S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "COST"]/VALUE/text())[1]', ...


10

Thanks to @Tom V for identifying this blog post that identifies the need for a temporary table. Adapting the ideas in the blog post, this now works very quickly: /*************************** shred the Event Data into readable form ***************************/ DECLARE @xml XML; SELECT TOP(1) @xml = CONVERT(xml, xet.target_data) FROM ...


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 ...


9

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 ...


9

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


8

I get the same error when I do this: DECLARE @foo VARCHAR(32) = CHAR(0); -- 0x0000 SELECT @foo FOR XML PATH, TYPE; So, find all instances of DescFuncao or DescTempoExperiencia (sorry, you don't use table aliasing, so it's impossible to tell which table they come from) where the contents contain CHAR(0), and fix them. For example: UPDATE dbo.whatever ...


8

Part 1: Proper XML Struture In order to get anything other than a flat XML layout, you need to use either FOR XML EXPLICIT mode, or nested FOR XML AUTO queries. Please see the following MSDN sections for complete details, including examples: Use EXPLICIT Mode with FOR XML Use Nested FOR XML Queries Also, you do not need to generate the ...


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

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 ...


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 ...


7

This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question. There is no obvious choice ...


6

You need to query for nodetype 3 to get the element text. I have changed your xpath expression slightly so it just returns the node text EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlDocument Select [text] from OPENXML (@hdoc,'/ReportHost/HostProperties/tag/text()',3)


6

The line: ... SELECT [CodFuncionario], STUFF ( ( SELECT ' / ' + CAST ( [DescFuncao] + '-' + [DescTempoExperiencia] AS VARCHAR(MAX) )... Should be: ... SELECT [CodFuncionario], STUFF ...


6

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:


6

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially: UPDATE existing rows INSERT missing rows You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted. Beyond that there are different ways to ...


6

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this: UPDATE yourTable SET yourXML = '<yourNewValidXML/>' WHERE rowId = 1 If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values. Here's a ...


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

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, ...


5

A possible solution to your question is neither elegant or clean (IMHO), but it does satisfy what you are looking for: SELECT ISNULL(s.lmx, '<Users></Users>') FROM ( select 1 as tag, null as parent, FirstName as [User!1!FirstName!Element], LastName as [User!1!LastName!Element] FROM Users FOR XML ...



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