Skip to main content

Questions tagged [xml]

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

Filter by
Sorted by
Tagged with
40 votes
2 answers
16k 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)[...
Martin Smith's user avatar
  • 84.9k
9 votes
2 answers
73k 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....
B.McCarthy's user avatar
21 votes
2 answers
222k 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 ...
Mark Sinkinson's user avatar
14 votes
1 answer
2k 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, ...
Hannah Vernon's user avatar
  • 70.2k
19 votes
2 answers
4k 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 ...
Alex Shelemin's user avatar
11 votes
1 answer
14k 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 ...
GMalla's user avatar
  • 189
11 votes
3 answers
25k 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 ...
Marcello Miorelli's user avatar
7 votes
1 answer
2k 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 ...
Bogdan Bogdanov's user avatar
5 votes
1 answer
3k views

Read system_health event_file instead of ring_buffer for deadlocks extended events

I'm currently using this disaster to locate recent deadlocks by reading the system_health Extended Events ring buffer. select top 2000000000 XEvent.value('@timestamp', 'datetime2(3)') as ...
crokusek's user avatar
  • 2,120
2 votes
1 answer
26k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> <name>tom&...
rahim asgari's user avatar
32 votes
1 answer
5k 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....
Gottfried Lesigang's user avatar
17 votes
2 answers
97k 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 ...
Gareth's user avatar
  • 313
13 votes
4 answers
42k 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?
pooja's user avatar
  • 311
13 votes
1 answer
817 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 ( ...
Mikael Eriksson's user avatar
10 votes
1 answer
68k 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 ...
Mike's user avatar
  • 747
10 votes
1 answer
30k 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 ...
John Hennesey's user avatar
7 votes
2 answers
1k 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" ...
Riley Major's user avatar
  • 1,903
6 votes
2 answers
16k 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 ...
itsAftab's user avatar
  • 159
5 votes
1 answer
1k views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
Bogdan Bogdanov's user avatar
5 votes
2 answers
2k views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- --...
Lazer's user avatar
  • 3,361
5 votes
2 answers
3k views

Import XML files into SQL Server 2012

Every Friday I have to import a couple (sometimes more than 300) of XML files into 2 tables. The structure of one of the tables, R000000, looks like this: R00000010 | R00000020 | R00000030 | ...
Eudes's user avatar
  • 169
5 votes
3 answers
5k views

How to insert multiple records using XML in SQL server 2008

i use SQL server 2008 in my web application back end. Apparently i iterate through all the records from the C# code whenever there is a multiple insertion scenario. i have never tried the multple ...
Jishnu A P's user avatar
4 votes
1 answer
6k views

Querying XML nested nodes

I need to integrate a daily XML file into my system. The XML file has an schema similar to this: create table txml(id int identity, data xml); insert into txml (data) values (' <order> <...
McNets's user avatar
  • 23.8k
4 votes
1 answer
4k views

SQL 2012 CLR error: "The context transaction which was active before entering ..."?

I am experience strange error with SQL 2012 and CLR: The context transaction which was active before entering user defined routine, trigger or aggregate "My routine" has been ended inside of it,...
Bogdan Bogdanov's user avatar
4 votes
1 answer
3k views

Bringing web service data into SQL server

I need to retrieve a data set daily from a hosted Web service. I can retrieve the XML "column names" in an SSIS package. But I need to retrieve all the data in the data set in csv format or bring ...
William M Low's user avatar
4 votes
3 answers
5k views

XML queries with case insensitive tag

I have a requirement where i want XML tags to be case insensitive in my xml queries. For example: DECLARE @myTable TABLE ( yourXML XML ) INSERT INTO @myTable SELECT '<z><a><b>1&...
IT researcher's user avatar
4 votes
1 answer
756 views

Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

I created a Extended Event to monitor and tune a Selective Xml Index CREATE EVENT SESSION [SelectiveXmlIndexing] ON SERVER ADD EVENT sqlserver.selective_xml_index_no_compatible_sql_type, ADD EVENT ...
KeyboardFriendly's user avatar
3 votes
2 answers
1k views

How to convert xml entities to unicode characters (with read-only access) in Oracle?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access. Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain ...
nutty about natty's user avatar
3 votes
2 answers
686 views

XML/SQL Server 2008 Error: XQuery...Cannot implicitly atomize or apply 'fn:data()' to complex content elements

Update 10/30/2018. Just in case anyone is interested, I have started to document at the end of this post what I'm actually doing to solve this, just because I've found some interesting things. ...
Dan's user avatar
  • 167
3 votes
2 answers
20k views

Trigger to audit table changes

I have created a trigger to audit changes to a table: CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] ON dbo.CPTCategoryMaster AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; ...
rks_dotnet's user avatar
3 votes
1 answer
2k views

Where to find the size of SQL Server data types?

This question has already been asked here but none of the answers was really helpful in finding out the size for example, of a XML variable. declare @myVariable XML I have learned from this question:...
Marcello Miorelli's user avatar
2 votes
1 answer
3k views

Import XML data while creating IDs with relationships

I need to parse XML from a file into tables while retaining their relationships through custom-generated ID values. For example, if I had the following XML: <root> <construction> ...
Kahn's user avatar
  • 1,813
2 votes
2 answers
3k views

Collation conflict when running dynamic sql FOR XML PATH

I am getting the following error message when running the query below: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation This ...
Marcello Miorelli's user avatar
2 votes
2 answers
715 views

How to query the XML data type? - Example

I know this question might have already been asked, but I am still trying to figure it out how to query the xml data and I really need an example. I have the following XML code that is inside a ...
Marcello Miorelli's user avatar
2 votes
1 answer
3k views

what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?

following the conclusion of the question how to test if XML = '' in sql server? to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode. It ...
Marcello Miorelli's user avatar
1 vote
1 answer
392 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
IT researcher's user avatar
1 vote
1 answer
796 views

Enumerating JSON arrays with OPENJSON

I've been working on converting some of my XML based stored procedures to JSON, but I'm running into an issue. With OPENXML, I had access to mp:id, so when I was parsing an XML, I could access an ...
Rushabh Mehta's user avatar
0 votes
1 answer
1k views

Convert ASCII Bytes from XML to VARCHAR

I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried: DECLARE @testxml XML = N' <...
user1952162's user avatar
0 votes
2 answers
58 views

ms sql query multiple table colums as identical tag

I am using SQL Server Express 2017 My Table looks like this +-------------+ | ID | Name | +-------------+ | 1 | Bert | | 2 | Agnes | | 3 | Diane | +-------------+ I'm looking for this kind ...
GeorgeDuke's user avatar
0 votes
3 answers
1k views

How to allow for ampersand in XMLtable()?

Starting with this Q&A, note that while this works: with data as ( select 'Hallöle sloven&#264;ina Hallöle sloven&#273;ina' str from dual ) select a.str, b.str2 from data a, xmltable( '/...
nutty about natty's user avatar
-1 votes
1 answer
528 views

SQL optimization for XML Path [duplicate]

SELECT distinct STUFF( (SELECT distinct ',' + hg.CODE FROM SP_HD hg Inner join Purchase.SP_DT on GOODS_RECEIPT_ID = hg.ID WHERE PO_DT_ID = d.PO_DT_ID FOR XML PATH(''), TYPE )....
Subin Benny's user avatar