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 =
'<...
- 22.2k
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, ...
- 69.5k
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 ...
- 22.2k
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 ...
- 22.2k
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]', '...
- 22.2k
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 (//...
- 22.2k
12
votes
Accepted
Is "ROW_NUMBER() OVER(ORDER BY xml.node)" well defined?
No, it's not documented and therefore not guaranteed.
In practice, it probably is guaranteed as I described in a Stack Overflow answer (reproduced below) but that doesn't meet your stated desire for ...
- 90.3k
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". ...
- 1,953
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 ...
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('.', '...
- 22.2k
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"&...
- 70.5k
11
votes
Accepted
XML Query keeps converting < to < and > to >
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(...
- 22.2k
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() ...
- 22.2k
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 '...
- 22.2k
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 ...
- 69.5k
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/@...
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 ...
- 22.2k
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 ...
- 25.1k
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 ...
- 13.2k
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 ...
- 22.2k
8
votes
Accepted
Read system_health event_file instead of ring_buffer for deadlocks extended events
This seems to work:
with XmlDeadlockReports as
(
select convert(xml, event_data) as EventData
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where object_name = '...
- 2,130
8
votes
Accepted
Querying XML nested nodes
You can add an additional CROSS APPLY to break out the rows in the way you require:
SELECT T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
...
- 402
8
votes
Accepted
What is the most effective SELECTIVE XML index?
How else can I determine the performance increase of my selective
index besides reviewing query execution times?
You could also look at IO utilization and CPU. Perhaps most interesting when dealing ...
- 22.2k
8
votes
How to write this SQL query for a XML type?
If you only need <Name>Tid</Name>, your query would look something like this:
DECLARE @x XML = N'
<Information>
<Groups>
<Group Name="Monitor">
<Items>
...
8
votes
Accepted
How to split XML array into separate rows (while upholding consistency)
Does something like this satisfy the resultset?
Table & Data
CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)
INSERT INTO #TestingIdea(...
- 16.5k
8
votes
XML Query Question
Does this get you what you want?
DECLARE @x XML = '
<SearchJobConfig>
<QueryID>1072</QueryID>
<QueryString>
<SearchCriteria name="Search query" >
<...
8
votes
Accepted
OPENXML to get content by using element and attribute name
You need to use a different XPath expression to match the attribute values, eg var[@dimension="task"]/string.
Like this:
declare @xmldata xml
set @xmldata =
'<?xml version="1.0&...
- 47.7k
7
votes
Accepted
Why store XML Schema in SQL Server 2012 database?
XML Schemas aren't stored in SQL Server so that an application can access them. The reasons for storing XML Schema Collections are found in how they improve working with the XML data.
According to ...
- 69.5k
7
votes
Accepted
How To Read HTML code as XML and get the output like the sample in sql?
This is not exactly elegant but seems to do the job.
DECLARE @X XML = REPLACE(REPLACE(@S, '<h4>', '<foo><h4>'), '</ul>', '</ul></foo>')
SELECT Category = x.value('...
- 86.5k
Only top scored, non community-wiki answers of a minimum length are eligible