Questions tagged [xquery]

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

Filter by
Sorted by
Tagged with
-1 votes
1 answer
57 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:...
sssjia24's user avatar
2 votes
1 answer
317 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....
Sir Swears-a-lot's user avatar
8 votes
4 answers
695 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. ...
Erik Darling's user avatar
  • 40.5k
7 votes
2 answers
809 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&...
uberbloke's user avatar
  • 108
2 votes
2 answers
464 views

How to parse XHTML node value with TSQL?

I have the following XHTML and would like to be able to parse out My Node Value. Is there a way to easily do this with TSQL? <SPAN style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM:...
user2368632's user avatar
  • 1,087
3 votes
1 answer
329 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 ...
JF Landry's user avatar
0 votes
1 answer
336 views

How to avoid implicit conversion when using XQuery value() method in TSQL?

I have the following xml that I am parsing using TSQL - XQuery value method. <Root> <FooAudit> <BarIntField>..</BarIntField> <BarDateTimeField>..</...
user2368632's user avatar
  • 1,087
0 votes
1 answer
62 views

Xquery Speedup/Optimization

I am using Sql server 2008 R2. To speed up the query execution in sql i have changed xquery part of it.I Have used cast method to query xml column and i got following results. DBCC DROPCLEANBUFFERS ...
IT researcher's user avatar
1 vote
1 answer
628 views

Improve querying of Extended Events target file

As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. ...
Krishn's user avatar
  • 13
0 votes
1 answer
5k views

XQuery in SQL Server to convert XML column data to relational data table

I previously asked a question about an error I was recieving. You don't really need it to understand this question but it's here for reference: XML/SQL Server 2008 Error: XQuery...Cannot implicitly ...
Dan's user avatar
  • 167
6 votes
2 answers
234 views

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 <ROW> Update BalanceTable set [...
IT researcher's user avatar
9 votes
1 answer
1k views

Using XQuery to filter out some children, but preserve parents

SET NOCOUNT ON; DECLARE @xml AS Xml = '<a><b>bbb</b><c>ccc</c><d>ddd</d></a>'; SELECT @xml; SELECT @xml.query('/a/*[self::b or self::c]'); SET @xml....
Cade Roux's user avatar
  • 6,602
1 vote
1 answer
2k views

Use xQuery to extract attribute values that also exist in SQL variable

XML structure: <ns0:message xmlns:ns0='xxx:testing'> <ns0:field name='AAA'>...</ns0:field> <ns0:field name='BBB'>...</ns0:field> <ns0:field name='VVV'>...<...
GeorgeCostanza's user avatar
0 votes
1 answer
673 views

XQquery for specific attribute of all specific elements in XML

I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML: <ROOT> <CATEGORY name="A"> <PRODUCT NetAmount="10.50"/> <...
Magier's user avatar
  • 4,777
3 votes
1 answer
795 views

Why is an empty XML node xml datatype not being recognized in this query?

My query seems to not recognize self-closing or empty nodes at all. Shouldn't the empty <td/> node return 'null'? Or an empty string. Even if I place an empty space in the node, it still is ...
user2566049's user avatar
8 votes
2 answers
793 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"') ...
S S's user avatar
  • 81
14 votes
3 answers
6k 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: ...
Andriy M's user avatar
  • 22.9k
6 votes
1 answer
4k 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 ...
Chessbrain's user avatar
  • 1,183
6 votes
3 answers
2k 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 ...
mhep's user avatar
  • 740
5 votes
1 answer
554 views

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 ...
reluctant and angry dba's user avatar
10 votes
2 answers
4k views

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

Consider the following simple XML: <xml> <customer name="Max"> <email address="[email protected]" /> </customer> <customer name="Erik"> <email address="erik@...
Hannah Vernon's user avatar
  • 69.9k
1 vote
1 answer
391 views

Update XML metadata in SQL Server

I'm trying to change the value in the XML metadata using SQL Server. The XML file looks like this: <Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/...
BuahahaXD's user avatar
  • 1,405
3 votes
1 answer
4k views

XML query filter by element name

I have the following structure with a variable number of elements on the 2nd level under root with unknown names: <root> <A>text a</A> <B>text b</B> </root> ...
Magier's user avatar
  • 4,777
2 votes
1 answer
3k views

Trim a value in an xml column in MS SQL Server

I have a database with an XML column. I am trying to update (trim) an xml value of the xml column. I am running on Windows, SQL Server 2008 Express. I did not find any way to trim directly in Xquery ...
IT researcher's user avatar
1 vote
1 answer
1k views

Select row from non-empty node value in xml column using xml query

I am trying to select the rows from the table by putting the condition on the xml column. The condition like if there is date value on the node ‘Completedon’ in the xml column then select that row. ...
IT researcher's user avatar
1 vote
1 answer
303 views

Xquery, insert, Trigger and no place to squeeze stored procedure

I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then ...
AcePL's user avatar
  • 349
2 votes
1 answer
167 views

Howto query xml

My source data in a SQL Server table has XML structured data. For each of the source rows I need to query the following sample XML structure in a way that I get a calculated value of all A sub ...
Magier's user avatar
  • 4,777
6 votes
1 answer
3k views

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 ...
Sebastian Meine's user avatar
5 votes
2 answers
14k views

Parsing Typed XML with a default namespace and inconsistent root node

I have a structured, typed xml file. <WebService xmlns="http://www.orbis-software.com/WebSvcCon"> <NewLeads> <OutputSchema> <root xmlns="" type="array"> <item type="...
AcePL's user avatar
  • 349
11 votes
4 answers
4k 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"&...
Wojteq's user avatar
  • 225
11 votes
1 answer
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:...
Mr.Brownstone's user avatar
1 vote
2 answers
6k views

Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server

So this is what I have so far: --Read xml content into a XML data type variable DECLARE @FileData XML SELECT @FileData = CONVERT(XML, BulkColumn) FROM OPENROWSET(BULK '\\file_path\test.xml', ...
erraticMigrant's user avatar
3 votes
1 answer
4k views

XMLDB - Nodes to Rows

I have an application that stores XML as XMLType in a table. This is the first time I'm using XMLDB and XMLType features, and was wondering if I could use the results from an XQuery FLWOR expression ...
romatthe's user avatar
  • 221
0 votes
1 answer
625 views

Indexing a view with xQuery in it

I'm not sure if this can be done at all but if it can it would be a huge help. I have a system where I am not allowed to make changes to the database structures. I have yet to have code changes put ...
Kenneth Fisher's user avatar
2 votes
2 answers
1k views

Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements

Suppose I have the following XML data, where <root> can have an arbitrary number of <child> children <child> can have an arbitrary number of <grandchild> children <...
cubetwo1729's user avatar
-1 votes
1 answer
2k views

default value with select case on xml

select case 'NULL' when 'NULL' then 'f' end as out output: f but select case (select (SELECT LEFT(l.list,LEN(l.list)-1) FROM (SELECT o.Reciting_Path + '|' + o.Declaimer +',' AS [text()] ...
Ario's user avatar
  • 1,042
2 votes
1 answer
2k views

XML queries with case insensitive tag- which is the best query

I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution. So table was something like DECLARE @myTable TABLE ( yourXML ...
IT researcher'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
8 votes
1 answer
434 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</...
IT researcher's user avatar
1 vote
2 answers
46k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...
IT researcher's user avatar
0 votes
1 answer
1k views

xml queries with exist and contains giving unexpected results

I have a query as shown below. DECLARE @tbl TABLE ( id INT, col XML ) INSERT INTO @tbl VALUES (1,'<Root> <Row> <User>xyz</User> <Rowid&...
IT researcher's user avatar
2 votes
2 answers
4k views

Remove scientific notation from an XML column

I have a XML column as shown below: <Root> <Row> <Rowid>1</Rowid> <date>2013-05-06</date> <Balance>1.0002E7</Balance> </Row> </...
IT researcher's user avatar
4 votes
1 answer
4k views

How to Return XML Node Ordinal, or delete node based on element value?

I have an XML document similar to this: <Root> <Sub> <Record> <Guid>aslkjflaksjflkasjfkljsd</Guid> </Record> <Record> <Guid>...
JNK's user avatar
  • 17.9k