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