Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [xquery]

XQuery (XML Query) is the W3C method to Query XML.

For more information consider checking out the Wikipedia entry for XQuery.

44 questions
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
11
votes
4 answers

Modify XML: attributes into elements

I have an XML column that contains data with similar structure: …
Wojteq
  • 225
  • 2
  • 6
11
votes
1 answer

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

Given a element, defined within an XML Schema Collection as such: …
Mr.Brownstone
  • 13,242
  • 4
  • 38
  • 55
10
votes
2 answers

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

Consider the following simple XML:
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
9
votes
1 answer

Using XQuery to filter out some children, but preserve parents

SET NOCOUNT ON; DECLARE @xml AS Xml = 'bbbcccddd'; SELECT @xml; SELECT @xml.query('/a/*[self::b or self::c]'); SET @xml.modify('delete /a/d'); SELECT @xml; Gives the following…
Cade Roux
  • 6,684
  • 1
  • 33
  • 55
8
votes
1 answer

IN and NOT IN for XML column

I have a table with a xml column. Xml is similar to abc 1 vf 2 ert 3 …
IT researcher
  • 3,168
  • 15
  • 59
  • 82
8
votes
1 answer

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. Everything needs limitations to prevent misuse.…
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
8
votes
4 answers

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. Since 1 or more SQL Handles may be involved, querying…
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
8
votes
2 answers

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"') WHERE identifier=5
S S
  • 81
  • 2
7
votes
2 answers

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 = '1' select @wibble.query('//wobble[1]') Is there anything I can do to…
uberbloke
  • 118
  • 4
7
votes
3 answers

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 NULL , Value1 VARCHAR(100) SPARSE NULL ,…
mhep
  • 750
  • 7
  • 22
6
votes
2 answers

Optimize/Speedup query

Below query is used for inserting and updating the tables in the SQL Server database. The XQuery is slow while executing in SSMS for first time. Query insert new Update BalanceTable set [daily_balance].modify(' insert…
IT researcher
  • 3,168
  • 15
  • 59
  • 82
6
votes
1 answer

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 contents look like this There are of course multiple…
Chessbrain
  • 1,223
  • 3
  • 17
  • 25
6
votes
1 answer

How to cast a list of values within xquery in SQL Server?

I have the following T-SQL code that I am trying to get to work: IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'TST') DROP XML SCHEMA COLLECTION TST; GO CREATE XML SCHEMA COLLECTION TST AS N'
Sebastian Meine
  • 9,163
  • 1
  • 28
  • 32
5
votes
1 answer

How to parse query plans to get a count of nested loop operators with unordered prefetch?

My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans so performance isn't very important. I tried…
reluctant and angry dba
  • 103
  • 7
1
2 3