Questions tagged [xml]
Short for 'eXtensible Markup Language.' A textual markup language for transferring structured data.
381 questions
1
vote
1
answer
42
views
How to extract indeterminate attributes from xml in a postgres db?
I've inherited a legacy DB in which excel data is stored in a text column of a table in a postgres DB. A value from that column might look like:
<Sheets>
<Sheet1>
<Addresses E54=&...
1
vote
1
answer
144
views
How to convert single column with XML data into multiple columns, while querying Extended Events XEL file
We have created an Extended Events session to capture errors with severity 16 or higher:
CREATE EVENT SESSION [Error_Reporting] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver....
- 5,964
12
votes
1
answer
946
views
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 ...
- 231
0
votes
2
answers
250
views
how to convert nvarchar(max) or varchar(max) to xml in sql server
I am trying to convert msdb jobs messages to xml.
I am using Ola Hallengren backup routines.
when I run the following routine it works amazing and converts everything.
now, if I change the number 3333 ...
- 16.6k
8
votes
1
answer
2k
views
XML Query keeps converting < to < and > to >
bigxml4u
Some query plans are too large to be properly stored as XML. You get the error: XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
Which is fine. ...
-1
votes
2
answers
608
views
Is there a simple way to filter XML datatype in SQL-Server?
I have a table in an SQL-Server database, containing a column, called XmlMsg, being of the datatype Xml:
"Columns" definition:
I would like to filter on that column, which first I did using ...
- 377
-1
votes
1
answer
81
views
SQL Query content from XML issue
This is the XML table I need to query.
And I need to query the 52324672-bd54-44cf-b836-869d39d64bd8 from value tag.
I tried to query like this
But it is not working as the issue looks from the i:...
- 1
3
votes
1
answer
637
views
How does the XML_COMPRESSION option work?
XML_COMPRESSION has recently gone GA in Azure SQL Database
I've been trying to find some details about how it works to understand the pros and cons and so far not found any specifics.
Trying the below ...
- 86.5k
2
votes
1
answer
322
views
Converting VARCHAR to XML with a size restriction
I have a query that needs to return text as valid XML.
I also need to restrict the length of the text to a specific number of characters.
I adapted a function that performs the conversion and outputs ...
- 23
0
votes
1
answer
499
views
Generate XML for each group in the table
I have the below data which I need to group with buid, duid and aid
INSERT INTO XmlTable (id, cid, aid, buid, duid)
VALUES(624220, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(625475, 68487905, 33, '...
- 197
-1
votes
1
answer
248
views
XML Reader Performance
I am trying to optimize a query and it seems that this code, specifically the XML Reader is causing the issue.
insert into #temp1 (Items, ApplicationId)
select distinct convert(varchar(2000),z.query('....
- 115
1
vote
1
answer
74
views
How to get more then one concatenated column from table rows
Below is a code i use now.
In real life @stab table is big (and it is normal table not variable) and there are many concatenated columns, not only 2.
I wonder is there any better solution than I use ...
- 13
2
votes
2
answers
2k
views
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels
This is the error message I get when running the query below:
Msg 6335, Level 16, State 102, Line 43 XML datatype instance has too
many levels of nested nodes. Maximum allowed depth is 128 levels.
...
- 16.6k
0
votes
1
answer
220
views
SQL: Count number of distinct records based on attribute value in an xml
Say I have following xml
<Operations>
<Info Id="2265" cId="2" aId="5" />
<Info Id="2266" cId="2" aId="5" />
<Info ...
- 197
2
votes
2
answers
339
views
Can't read data out of XML stored in table column
I have some data stored in a table in XML format:
<AccountTypes xmlns="">
<Id>1003</Id>
<Id>2</Id>
<Id>3</Id>
<Id>1004</Id>
&...
- 302
-1
votes
2
answers
1k
views
XML data type cannot be compared or sorted
My source is in SQL server; my target is in Snowflake. The target table was created by extracting the XML column only from the source. I need to compare the first 20 rows in source and target to see ...
- 65
0
votes
1
answer
258
views
What is the best option to store XML in SQL Server 2016?
On SQL Server 2016 (13.0.6300.2) we have one table that have two XML columns and around 1 500 000 rows. Size of table is around 150 GB. What is the best option to compress this table ? I was checking ...
- 421
3
votes
1
answer
262
views
XML vs UDF performance in SQL Server
I have a situation where I have to decide between receiving XML as an input parameter to the stored procedure or a list of comma separated values and parse them using a multi-table valued function.
...
- 601
2
votes
1
answer
489
views
SQL Server XQuery Namepace
I'm trying to query an xml like below.
<wfs:FeatureCollection xmlns:gml="http://www.opengis.net/gml" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis....
- 3,243
8
votes
4
answers
794
views
Return Variable Number Of Attributes From XML As Comma Separated Values
over-extended
In SQL Server Extended Events for the blocked process report and deadlock XML, it's possible to get multiple SQL Handle values back to identify queries involved in the raised event.
...
-1
votes
1
answer
587
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
)....
- 67
0
votes
1
answer
223
views
Retrieve execution plan via XML data from sp_WhoIsActive in adhoc process
We have installed a process in which we execute the sp_WhoIsActive procedure every 1 minute. We combine some additional logic into the same job.
Currently the sp_WhoIsActive procedure is run every 4-5 ...
- 421
4
votes
1
answer
812
views
SQL Server execution XML plan cut off in length
I am running the following standard query to retrieve the XML execution plan for queries that I ran on my SQL Server via command line.
SELECT pl.query_plan FROM sys.dm_exec_cached_plans ep CROSS APPLY ...
- 141
0
votes
1
answer
1k
views
Loading Extended Events ring buffer to SQL table using powershell
I am running a RPC_Completed extended event which outputs to a ring buffer.
While trying to read the data from the ring buffer target data, xQuery is taking too much of time.
The faster route was to ...
- 11
1
vote
1
answer
3k
views
json_modify on condition
As in my previous question, I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify
I'm used to using .modify with XPATH to be able to modify XMLs on the ...
- 251
1
vote
1
answer
1k
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 ...
- 251
1
vote
2
answers
88
views
Execution time/cost of function in assembly
I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
- 6,612
0
votes
2
answers
396
views
like in exist() xml mssql
By following question: See if XML element exists at any level in document with a specific value
Is there a possible solution to implement like clause within exist?
For example I have similar structure ...
0
votes
1
answer
394
views
Merging two XML variables - please help!
I am using SQL Server Management Studio 17.9.1 with SQL Server 2016 Service Pack 2 (SP2).
There is no table structure to show, just two XML variables that contain XML, as shown below:
DECLARE @A XML = ...
- 21
1
vote
0
answers
152
views
SQL Error 6304 - XML parsing: An unexpected error has occurred in the XML parser
I'm running SQL Server 2019 and not sure if it's related to this, but on SQL Server 2014 if I create a table as follows
CREATE TABLE MyTable
(
FieldA smallint NOT NULL,
FieldB xml NOT NULL
)
And ...
- 11
0
votes
1
answer
92
views
Do we have information about duration time of query in actual execution plan?
I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
- 45
7
votes
2
answers
874
views
SQL Server / XML.query() causes massive memory grant
The following (on my systems, tested on SQL Server 2016 and 2019 std) produces a massive memory grant request (3327 MB)
declare @wibble xml = '<wibble><wobble>1</wobble></wibble&...
- 108
3
votes
1
answer
1k
views
What are the benefits of using XML over CSV files in regards to data dumps? [closed]
Prologue
I've noticed that one benefit of using CSV over XML for data dumps is the fact that fewer disk space is used. See minimal working example below.
Let's suppose we have the following dataset. ...
- 131
3
votes
1
answer
380
views
Properly pivot an XML column with XQuery
I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.
To get started, this is what the XML column looks like in SQL:
<Model ...
- 55
5
votes
1
answer
3k
views
Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan
I like to find missing indexes on the go, looking at the execution plans!
It can potentially give me an indication where further to look at if I want to improve something that is currently running.
...
- 16.6k
1
vote
3
answers
909
views
How can parse column names using ordinal position (not name) from XML data in SQL Server?
I have several hundred tables in a database that have the same structure:
SomeId, Pos, Varying Number of Other Fields
So, for example, one table may look like this:
PersonId, Pos, Hobby, Degree
12345, ...
- 135
0
votes
0
answers
76
views
How should I deal with sub attributes of xml data when loading onto mysql?
I have some xml files (example below). As you can see in the xml, id 4 has 3 entries for tax. When I load it right now, only the last entry gets inserted into the table.
How can I include all 3 of ...
9
votes
2
answers
407
views
Why is SQL doing an index scan and seek with the same XML query structure?
I've set up a test to benchmark xml performance in SQL server.
Test Setup
One Million rows of data
XML defined Column with primary key
A primary XML index
A secondary XML index on the path
XML data ...
- 213
0
votes
0
answers
347
views
How to open .bin SQL database?
I'm stuck and not sure if somebody had a similar issue.
I have an old database stored as .bin files. It dates back to 2012. I can see an xml catalogue file which indicates to me that it is some kind ...
- 1
0
votes
1
answer
619
views
sqlserver - Insert from xml generated with `select for xml`
Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different ...
2
votes
0
answers
2k
views
EAV vs JSON/XML column approach to store complex user form data in a database? [closed]
We are working on an internal tool which will essentially be a complex, power-user oriented form app. It is not a dynamic field app - users can't define their own fields.
Form field types: The form ...
- 166
6
votes
1
answer
2k
views
OPENXML to get content by using element and attribute name
In this SQL Server OPENXML example:
declare @xmldata xml
set @xmldata =
'<?xml version="1.0"?>
<wddxPacket version="1.0">
<data>
<var ...
- 163
0
votes
1
answer
292
views
Get the last ID from Multiple Stored Procedures Executed
So I have an XML file , to take data from it and insert into tables.
For this I’ve created 3 procedures(INSERTXMLMultipleTables,INSERTXMLNIAC,INSERTNIACFORCOMPONENTS).
The logic of these procedure is ...
- 25
7
votes
4
answers
13k
views
Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute
I need to dig into a logs table with a schema similar to this:
CREATE TABLE t (
id int PRIMARY KEY,
data varchar(max)
);
Column data stores a XML text received from a web service in this format:
...
- 23.9k
3
votes
1
answer
676
views
Mix ;WITH NAMESPACES and schema collections?
I'm getting these xml files from a vendor, and it's a wrapper of NITF (news) schema and the http://www.xmlnews.org/namespaces/meta# news metadata schema (from Space 1999!)
Unfortunately, they don't ...
- 255
3
votes
1
answer
207
views
Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?
I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index ...
- 263
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Ĉina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/...
3
votes
2
answers
2k
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 ...
3
votes
1
answer
1k
views
How to add Spatial Reference ID 28992 to MSSQL server?
I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?
My end goal is to get this dataset into MS SQL Server, with the Geography Markup ...
- 131
0
votes
1
answer
181
views
SQL Server Deadlock OPENXML insert
I am running into a deadlock issue when I call a stored procedure in Sql Server 2014 Enterprise Edition. An XML string is passed into the stored procedure and can be called many times in quick ...
- 1