Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [xml]

Short for 'eXtensible Markup Language.' A textual markup language for transferring structured data.

XML (Extensible Markup Language) is a standard for marking up structured data for messaging or storage. It is descended from earlier standards - SGML and HTML

An XML document can be defined by a schema defining its legal structure, and most languages support libraries or functions for manipulating XML documents. Many database management systems also support storage of XML data, either in an opaque blob or a native data type that allows searches and indexing. However, XML fields in relational databases tend to perform poorly.

Some database management systems use XML based structures natively. and others e.g. (some document databases) can store XML documents.

Another commonly used markup notation is JSON (JavaScript Object Notation). A comparison of the two formats can be found here.

384 questions
40
votes
2 answers

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'), '',…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
32
votes
1 answer

Very strange performance with an XML index

My question is based on this: https://stackoverflow.com/q/35575990/5089204 To give an answer there I did the following test-scenario. Test scenario First I create a test table and fill it with 100.000 rows. A random number (0 to 1000) should lead to…
Gottfried Lesigang
  • 553
  • 1
  • 6
  • 13
23
votes
2 answers

Converting a VARCHAR to VARBINARY

I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising. However, it's come to the point where the query plans are taking up…
Mark Sinkinson
  • 10,657
  • 4
  • 47
  • 54
23
votes
5 answers

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

I have a big query (if necessary I will post it here) and I'm getting this error: Msg 6841, Level 16, State 1, Line 1 FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0000) which is not allowed in XML.…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
19
votes
2 answers

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

I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete numbers, let's say the table has a million rows…
Alex Shelemin
  • 555
  • 4
  • 11
17
votes
2 answers

sp_send_dbmail stored procedure send with attachment

I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the customer as an attachment. I'm looking for a more…
Gareth
  • 313
  • 1
  • 3
  • 9
17
votes
3 answers

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

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time to time; although not frequently; I'll need to query the XML to find element and attribute…
JoeGeeky
  • 1,297
  • 4
  • 16
  • 26
16
votes
3 answers

SQL server changes XML structure when inserted

I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server. Here is the data I insert …
Mr Zach
  • 347
  • 2
  • 7
14
votes
3 answers

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

I have an XML value like this: A B C ... I want to concatenate all I values and return them as a single string: ABC.... Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and…
Andriy M
  • 23,261
  • 6
  • 60
  • 103
14
votes
1 answer

SQL Server Query Plan XML: QueryPlanHash Length

This is definitely a bug. Details were on the old Connect site, and are no longer available, as it has been retired in favor of an indeterminate number of other solutions, both third and first party. While testing some changes to sp_BlitzCache (full…
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
14
votes
1 answer

How can I shred this Extended Events XML as fast as possible?

I created an Extended Events session in SQL Server 2008 R2. The session runs, and collects events as they happen, exactly like you'd expect. If I shred the xml when there are relatively few events, performance is acceptable. When I have thousands…
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
13
votes
4 answers

Read XML file in SQL Server 2008

How can I read an XML file and store the data in XML to our table in SQL Server 2008?
pooja
  • 311
  • 1
  • 2
  • 5
13
votes
1 answer

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( select top(10000) row_number() over(order by (select…
Mikael Eriksson
  • 22,295
  • 5
  • 63
  • 106
12
votes
3 answers

SQL to read XML from file into PostgreSQL database

How can I write SQL to read an XML file into a PostgreSQL XML value? PostgreSQL has a native XML data type with the XMLPARSE function to parse a text string to that type. It also has ways to read data from the filesystem; the COPY statement, among…
bignose
  • 1,252
  • 1
  • 9
  • 17
12
votes
1 answer

Is "ROW_NUMBER() OVER(ORDER BY xml.node)" well defined?

(It is more a question of documentation rather than behavior. It has been migrated from Stack overflow as was suggested there.) While researching the answer to another question that required extracting XML nodes in the original element order, I came…
T N
  • 231
  • 1
  • 7
1
2 3
…
25 26