33
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 estimator in SQL Server 2014. You can use a trace flag in SQL Server 2014 to force the old estimator and then you will see the same timing characteristics in SQL Server ...
answered Feb 26 '16 at 19:50
Mikael Eriksson
20.8k44 gold badges5454 silver badges9898 bronze badges
31
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 ...
answered Apr 23 '14 at 14:43
Aaron Bertrand
171k2424 gold badges360360 silver badges568568 bronze badges
25
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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'
select @X;
Result:
<root>
<...
24
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 simple ...
16
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 -- "...
answered Nov 2 '15 at 22:30
Solomon Rutzky
62.1k66 gold badges126126 silver badges253253 bronze badges
15
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, you would use either * (indicating that it should check all nodes of a particular level, but not other levels), or // (indicating that it should check all nodes ...
answered Oct 19 '16 at 1:51
Solomon Rutzky
62.1k66 gold badges126126 silver badges253253 bronze badges
14
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.
14
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
INNER JOIN dbo.TableB AS b
ON a.AccountId = b.AccountId
WHERE a.Section = o.Section
FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM ...
answered Apr 30 '16 at 3:55
Aaron Bertrand
171k2424 gold badges360360 silver badges568568 bronze badges
14
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 specifying column aliases for your dynamic pivot query.
SELECT @cols2 = STUFF((SELECT distinct ',' +
quotename(convert(char(10), [StayDate] , 120)...
answered Dec 20 '16 at 14:48
Mikael Eriksson
20.8k44 gold badges5454 silver badges9898 bronze badges
14
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 predicate on position, [1], to get the first occurrence also in document order.
select H4.X.value('(span/text())[1]', 'varchar(10)') as Section,
UL.X....
13
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 accomplish ...
answered Jan 17 '15 at 3:05
Solomon Rutzky
62.1k66 gold badges126126 silver badges253253 bronze badges
13
The line:
...
SELECT
[CodFuncionario],
STUFF
(
(
SELECT
' / ' +
CAST
(
[DescFuncao] + '-' +
[DescTempoExperiencia]
AS VARCHAR(MAX)
)...
Should be:
...
SELECT
[CodFuncionario],
STUFF
(
...
13
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>
...
12
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'')]))
...
answered Jul 13 '13 at 16:40
Mikael Eriksson
20.8k44 gold badges5454 silver badges9898 bronze badges
12
If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server.
Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is:
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
You would then attach the file ...
12
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
...
answered Jul 16 '15 at 18:47
Aaron Bertrand
171k2424 gold badges360360 silver badges568568 bronze badges
12
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 sys....
answered Nov 18 '15 at 16:22
12
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]', 'smalldatetime'),
@X.value('(Value/text())[1]', 'varchar(max)'),
@X.value('(Value/text())[1]', 'int'),
@X.value('(Value)[1]', 'smalldatetime'),
...
answered Nov 21 '17 at 12:20
Mikael Eriksson
20.8k44 gold badges5454 silver badges9898 bronze badges
11
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 not ...
11
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 expressions, including being able to use FLWOR Statement and Iteration
Data in XML variables and columns can be modified inline using XQuery expressions via XML DML.
...
answered Mar 26 '16 at 19:29
Solomon Rutzky
62.1k66 gold badges126126 silver badges253253 bronze badges
11
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". Your provided example, in fact, could be handled without the nested XML generation.
Let's make some test tables and data:
USE tempdb;
DROP TABLE IF EXISTS T1;
...
11
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. ...
answered Jun 12 '15 at 12:43
Mikael Eriksson
20.8k44 gold badges5454 silver badges9898 bronze badges
11
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]', 'nvarchar(100)'),...
11
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, 0x0010, 0x0011, 0x0012, 0x0013, 0x0014, 0x0015, 0x0016, 0x0017, 0x0018, 0x0019, 0x001A, 0x001B, 0x001C, 0x001D, 0x001E, 0x001F.
One workaround is to remove , ...
11
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 is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...
answered May 7 '16 at 17:27
Solomon Rutzky
62.1k66 gold badges126126 silver badges253253 bronze badges
11
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 @TableB AS table
(
AccountID char(2) PRIMARY KEY,
Name varchar(20) NOT NULL
);
Data
INSERT @TableA
(ID, Section, AccountID)
VALUES
(1, 'shoes', '...
answered May 1 '16 at 8:18
11
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 your string back as xml.
11
I think this is happening because that one hash is an odd number of characters. A valid VARBINARY is going to need to have an even number of "pairs" to correctly represent the data. So... you should be able to solve this by removing the 0x, putting a '0' at the beginning, grabbing the right 18 characters, and then casting it to VARBINARY.
CONVERT(VARBINARY(...
11
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 email address has an @ in it. Parsing the XML nodes out lets you check individual emails for it.
DECLARE @x XML
= '<xml>
<customer name="Max"><...
answered Apr 25 '18 at 14:38
Erik Darling
31k1313 gold badges101101 silver badges188188 bronze badges
11
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('.', 'varchar(50)')
If you want the values for I and J separate do this instead.
select @MyXml.query('/R/I/text()').value('.', 'varchar(50)'),
@MyXml.query('/...
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
xml × 352sql-server × 200
sql-server-2012 × 43
oracle × 35
t-sql × 31
sql-server-2008 × 29
xquery × 25
postgresql × 23
performance × 22
sql-server-2008-r2 × 22
mysql × 17
sql-server-2014 × 14
oracle-11g-r2 × 14
import × 14
sql-server-2005 × 13
execution-plan × 13
sql-server-2016 × 11
database-design × 9
stored-procedures × 8
sql-clr × 8
index × 7
ssis × 7
datatypes × 6
xsd × 6
functions × 5