The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [xml]

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

Filter by
Sorted by
Tagged with
33
votes
2answers
13k views

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)[...
32
votes
1answer
4k views

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....
19
votes
2answers
2k views

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 ...
18
votes
4answers
20k views

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 ...
17
votes
2answers
134k views

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 ...
15
votes
3answers
1k views

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 <xsl:value-of select="...
14
votes
3answers
931 views

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

I have an XML value like this: <R> <I>A</I> <I>B</I> <I>C</I> ... </R> I want to concatenate all I values and return them as a single string: ...
13
votes
2answers
48k views

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 ...
13
votes
1answer
1k views

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, ...
12
votes
4answers
2k views

How to store data in a database without tables?

All I learned in school was SQL which saves data to tables. Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG). ...
12
votes
3answers
27k views

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 ...
12
votes
4answers
3k views

When to use XML data type

I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the ...
12
votes
4answers
41k views

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?
12
votes
1answer
492 views

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 ( ...
11
votes
4answers
3k views

Modify XML: attributes into elements

I have an XML column that contains data with similar structure: <Root> <Elements> <Element Code="1" Value="aaa"></Element> <Element Code="2" Value="bbb"&...
11
votes
2answers
3k views

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

I have HTML code stored in the data base, and I want to read it as XML. My codes: http://rextester.com/RMEHO89992 This is an example of the HTML code I have: <div> <section> &...
11
votes
2answers
2k views

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

I am using this great example https://dba.stackexchange.com/a/25818/113298 from Bluefeet, to create a pivot and transform it to xml data. Declaring the param DECLARE @cols AS NVARCHAR(MAX), @query ...
11
votes
1answer
427 views

SQL Server Query Plan XML: QueryPlanHash Length

UPDATE: This is definitely a bug. For full details see this Connect Item. While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came across what I thought was a bug ...
10
votes
2answers
2k views

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

Consider the following simple XML: <xml> <customer name="Max"> <email address="me@you.com" /> </customer> <customer name="Erik"> <email address="erik@...
10
votes
1answer
2k views

Replace value of strongly typed xml element in SQL Server with XQuery

Given a element, defined within an XML Schema Collection as such: <xsd:element name="xid"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:...
9
votes
1answer
58k views

Simplest way to edit single entry in XML column?

As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.). What is the easiest option to replace a single ...
9
votes
1answer
10k views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we ...
9
votes
2answers
9k views

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 ...
9
votes
1answer
371 views

Variable for element name

I have this tSQL code which works OK: SELECT c.logguid, a.b.value('./PropertyValue', 'varchar(max)') asd FROM [dnn].[dbo].[EventLog2] c cross apply sss.nodes('/LogProperties/LogProperty[...
8
votes
1answer
5k views

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

I have an application that stores various user defined data points about a record in an XML column. I have no control over how these are stored or updated. When I query a column it can return 1 of 3 ...
8
votes
1answer
269 views

IN and NOT IN for XML column

I have a table with a xml column. Xml is similar to <Root> <Row> <user>abc</user> <Rowid>1</Rowid> </Row> <Row> <user>vf</...
8
votes
3answers
166 views

XML Query Question

I'm trying to generate a SQL query to get the "DATE" values out of the following SQL XML: I've tried something like this but I don't think I'm understanding the concepts. select xConfig.value('(/...
8
votes
2answers
2k views

XML parsing slowly

I'm currently trying to tune a somewhat problematic stored procedure and I've noticed that majority of cost associated with running it comes from parsing parsing some XML into a temp table. Here is ...
8
votes
1answer
2k views

Estimated vs Actual rows differences (actual much smaller than estimated) - sort

I'm running a query that is processing some nodes out of an XML document. My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some ...
8
votes
1answer
955 views

How to shred .docx XML?

I am trying to import a xml (actually a docx file) to a sql server 2008 database. I am almost a novice in XML programming. I googled a lot but almost all the examples there are with simple xml file. ...
8
votes
1answer
865 views

Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

Kenneth Fisher put a blog post out about how to determine What SQL Version is My SSIS Package? in April, 2015. It has a table of which SQL versions map to which PackageFormatVersion of the SSIS ...
8
votes
1answer
31k views

Converting XML to MySQL tables

I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this? ~~~~~~~~~~ <host starttime="1392325468" endtime="...
8
votes
1answer
3k views

Generate XML using Oracle XML schema

I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, ...
7
votes
2answers
100 views

XQuery doesn't update XML data

I am trying to run the script to update an XML column: UPDATE DataImpTable SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"') ...
7
votes
3answers
48k views

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

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE dbo....
7
votes
1answer
7k views

Avoiding entitized characters when using FOR XML PATH for string concatenation

I have this query: SELECT DISTINCT f1.CourseEventKey, STUFF ( ( SELECT '; ' + Title FROM ( SELECT DISTINCT ...
7
votes
1answer
13k views

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

Is it possible to query XML to find if a particular element has a certain value? For example, if I wanted to see if the below XML has the value "Brandt" in <ContactFName>. But note, the ...
7
votes
3answers
12k views

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

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter. Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a ...
7
votes
1answer
5k views

Can I use attribute name to identify XML element?

Rather than using the element index COLUMN[1]/@value below, is it possible to retrieve the column value by specifying an attribute name such as: COLUMN[@name=”Prod_ID”]/@value? When I tried, I got ...
7
votes
2answers
1k views

how to get estimated subtree cost?

If I have a query that returns a query_plan, for instance like this: SELECT TOP 1000 st.TEXT ,cp.size_in_bytes ,cp.plan_handle ,QP.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS ...
7
votes
1answer
1k views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
7
votes
1answer
236 views

Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

SET STATISTICS XML ON; DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')); Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect "CardinalityEstimate" ...
6
votes
2answers
545 views

How to write this SQL query for a XML type?

I am using SQL Server 2012 in which I have a table [abc] which has a column called [Information] of the [XML] type. This column has values like - <Information> <Groups> <...
6
votes
3answers
774 views

How to query against exact values in XML column set

I have a table that contains 80+ sparse columns along with a column set column, this is a brief example: DROP TABLE IF EXISTS #ColumnSet GO CREATE TABLE #ColumnSet ( Id INT NOT ...
6
votes
2answers
12k views

Exporting results to XML

I am trying to create an XML file from a SQL query. The out put should look like the image below Where the tag BranchID appears again as SubParentBranchID if there are more than one SubBranchID ...
6
votes
1answer
860 views

How to split XML array into separate rows (while upholding consistency)

I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve. In the XML File Posts.xml the ...
6
votes
2answers
590 views

Why high compile times for functions with XML on SQL Server 2016?

We have inline functions which gather up data into XML, pass that derived XML to other functions, which then shred it and reform it as a string. (Your "you shouldn't do that sort of stuff in T-SQL" ...
5
votes
3answers
5k views

T-SQL dynamically import XML into tables (SQL Server 2014)

I have a XML response with this structure but with some 150 different nodes: <?xml version="1.0" encoding="UTF-8"?> <Orders> <Order> <OrderID>334</OrderID> ...
5
votes
2answers
28k views

How do I import XML to a SQL Server table?

Can anyone help me work towards fixing my solution to import my XML data into a SQL Server Table? I have done my research, but this task has been very hard for me to accomplish. Everything I have ...
5
votes
3answers
4k views

Help with SQL Query to XML output

I have the following query: SELECT [ID] AS [NewID] , ( CASE WHEN LEN([MiddleName]) > 0 THEN [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.' ...