Skip to main content
33 votes
Accepted

Very strange performance with an XML index

There sure is a lot going on here so we will just have to see where this leads. First off, the difference in timing between SQL Server 2012 and SQL Server 2014 is due to the new cardinality ...
Mikael Eriksson's user avatar
26 votes
Accepted

SQL server changes XML structure when inserted

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here. For one node declare @X xml = '<...
Mikael Eriksson's user avatar
19 votes

Which datatype to store XML data in: VARCHAR(MAX) or XML

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column The factors are: The XML type is queryable / parseable through XQuery ...
Solomon Rutzky's user avatar
19 votes
Accepted

See if XML element exists at any level in document with a specific value

For this you want to use the .exist() XML function as it will return a BIT (i.e. boolean) value indicating whether or not the XQuery find anything. To handle the non-static location of an element, ...
Solomon Rutzky's user avatar
16 votes
Accepted

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well): SELECT DISTINCT o.section, names= STUFF(( SELECT ', ' + b.Name FROM dbo.TableA AS a ...
Aaron Bertrand's user avatar
14 votes
Accepted

When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

Attribute names in XML are not allowed to start with a number, see NameStartChar. You have to come up with alternative names for your attributes and encode that in a separate @cols variable ...
Mikael Eriksson's user avatar
14 votes

How To Read HTML code as XML and get the output like the sample in sql?

I am trying to establish communication between nodesh4 and ul. You can use the << and >> operator to check if a node is before or after another node in document order. Combine that with a ...
Mikael Eriksson's user avatar
14 votes
Accepted

How to return a null from an empty XML tag in SQL server using Xpath only?

Specify the text() node within the Value element. That node is missing when you have an empty tag. Try this: declare @X xml; set @X = '<Value/>'; select @X.value('(Value/text())[1]', '...
Mikael Eriksson's user avatar
14 votes
Accepted

SQL Server / XML.query() causes massive memory grant

With a quick look at your xpath expression //wobble[1] and the test data you have you are looking for the first wobble you can find. One could even think that your expression is equal to this (//...
Mikael Eriksson's user avatar
13 votes
Accepted

How to escape XML characters in TSQL before converting to XML?

Use for xml path to create XML instead of casting. select @YourVariable for xml path(''), type Empty string in the path expression and the absence of an alias on the returned column will give you ...
Mikael Eriksson's user avatar
12 votes

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I thought I would try a solution using XML. SEDE Demo Tables DECLARE @TableA AS table ( ID integer PRIMARY KEY, Section varchar(10) NOT NULL, AccountID char(2) NOT NULL ); DECLARE @...
Paul White's user avatar
  • 84.3k
11 votes

FOR XML could not serialize the data because it contains character (0x0000)

I tested this with ASCII characters 0-255 and found out that you get this error for characters: 0x0000, 0x0001, 0x0002, 0x0003, 0x0004, 0x0005, 0x0006, 0x0007, 0x0008, 0x000B, 0x000C, 0x000E, 0x000F, ...
jumxozizi's user avatar
  • 213
11 votes
Accepted

Create Nested XML in a Sql Server Indexed (Materialized) View

Short answer: You can't. Long answer: There's a few things going on here. First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". ...
Riley Major's user avatar
  • 1,893
11 votes
Accepted

LOB_DATA, slow table scans, and some I/O questions

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It ...
Solomon Rutzky's user avatar
11 votes
Accepted

Return xml sequences where an attribute doesn't contain a specific character

An easy way to do this is to use the nodes method to get right to the address attribute and check for your @ sign. The problem with the way you're looking now is that it's only checking that any ...
Erik Darling's user avatar
  • 40.8k
11 votes
Accepted

Concatenate all values of the same XML element using XPath/XQuery

This might work for you: select @MyXml.value('/R[1]', 'varchar(50)') It picks up all text() elements from the first R and below. If you just want all text() you can do select @MyXml.value('.', '...
Mikael Eriksson's user avatar
11 votes

XQuery doesn't update XML data

You'll need to declare the namespaces in the modify function. Something like this: DECLARE @xml xml = N'<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&...
Hannah Vernon's user avatar
  • 70.1k
10 votes

LOB_DATA, slow table scans, and some I/O questions

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not ...
Mikael Eriksson's user avatar
10 votes
Accepted

how to test if XML = '' in sql server?

You may check for NULL and nodes absence (exist method of xml type): @ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0 Your XPath can be more specific, if necessary: @ListNoteTypeCode ...
i-one's user avatar
  • 2,354
10 votes

How to write this SQL query for a XML type?

What you want here is not entirely clear from your question. My guess is that you want the value from the <Value> node where the <Name> node is Tid. Shred the XML using the nodes() ...
Mikael Eriksson's user avatar
10 votes

XQuery doesn't update XML data

If you have the XML in table you should use UPDATE instead of SET and there you can put the namespace declaration outside the XML_DML expression using WITH XMLNAMESPACES. with xmlnamespaces(default '...
Mikael Eriksson's user avatar
10 votes
Accepted

Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

The problem is that you are converting XML data, which is stored as Unicode (UTF-16 specifically), into 8-bit data. 8-bit data requires a code page to know which set of up to 256 characters to use (or ...
Solomon Rutzky's user avatar
9 votes
Accepted

How to parse query plans to get a count of nested loop operators with unordered prefetch?

This will Work Perfectly® WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ) SELECT qp.plan_name, qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@...
Erik Darling's user avatar
  • 40.8k
9 votes
Accepted

How to query against exact values in XML column set

Specifying the text() node before the predicate will be more efficient than having text() in the predicate. select * from #ColumnSet as C where AllValues.exist('*/text()[. = "POSITIVE"]') = 1 Query ...
Mikael Eriksson's user avatar
9 votes

SQL server changes XML structure when inserted

This page of the SQL Server documentation says The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not ...
Michael Green's user avatar
9 votes
Accepted

T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

This is by design. When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it ...
Mr.Brownstone's user avatar
9 votes
Accepted

Why is SQL doing an index scan and seek with the same XML query structure?

XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce. One of the columns in the ...
Mikael Eriksson's user avatar
9 votes
Accepted

XML Query keeps converting < to &lt; and > to &gt;

You are doing one level of select too many in the problematic part of your code. Your version simplified select ( select '<>' as [processing-instruction(query_plan)] ) for xml path(...
Mikael Eriksson's user avatar
8 votes
Accepted

how to get estimated subtree cost?

I believe you will have to do some XML query work to get that estimated cost. See if this is what you are looking for: ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/...
GoodwinSQL's user avatar

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