Stack Overflow
Stack Overflow

Questions tagged [xquery-sql]

the XQuery language as supported by database systems. Include the tag of your system to specify ("sql-server", "oracle", "postgresql", etcetera). Use the "xquery" tag on its own for general questions about XQuery.

XQuery is a query language designed to extract data from arbitrary XML documents. Multiple RDBMSes support XQuery as part of the SQL/XML standard or using proprietary syntax. What follows is a non-exhaustive list.

Microsoft SQL Server

In SQL Server 2005 and onwards, the xml data type methods, in particular .query(), support querying XML and returning the results as relational data. These methods support a subset of XQuery, with special support for some T-SQL specific constructs like variables -- see the XQuery Language Reference for specifics.

Oracle

Oracle includes XML support as Oracle XML DB. It supports XQuery both for querying and updating data, both relational and XML. The XMLQUERY function is used to issue XQuery queries within an SQL statement.

453 questions
5
votes
1 answer

How to replace a part of XML node in SQL Server 2005

I would like to know how I can update a part of the text of an XML node in SQL Server 2005 using xquery In the following example I would like to replace the word "very" with "excellent" declare @xml as xml set @xml = 'well hello…
Peter
  • 53
  • 1
  • 3
5
votes
2 answers

When querying XML in SQL Server 2005, multiple tags of the document are in the same row

Update to provide clarification Here's a full copy of one of the xml files I"m having trouble with. …
Neberu
  • 211
  • 1
  • 8
  • 19
5
votes
2 answers

xQuery LIKE-operator (starts-with)

Value in the field is as follows Parameter23 Parameter SQL query is select * from parametermaster where cast(ParameterName as xml).exist('en-US/text()[contains(.,"P")]') = 1 And I am trying to it as select * from…
Harsh Baid
  • 7,199
  • 5
  • 48
  • 92
5
votes
0 answers

query() vs value() in XQuery SQL XML

Which of the following is a better performing query? --query a Select * UserDefXml.query('/Product/ProductName/text()') From ProductsDB.dbo.ProductsTable --query b Select * UserDefXml.value('(/Product/ProductName)[1]','varchar(max)') From…
FMFF
  • 1,652
  • 4
  • 32
  • 62
5
votes
1 answer

shredding xml recursively into the database

I have the following XML data and the Element table. DECLARE @input XML = ' data1 data2 …
Fred Jand
  • 699
  • 7
  • 25
5
votes
1 answer

SQL Server creates an XML namespace when default is specified

Take this simple example: declare @myXml xml set @myXML = ' ' select t.c.query('.') from @myXml.nodes('/root/line') t(c) As expected, I get back three rows, looking like…
Cobus Kruger
  • 8,338
  • 3
  • 61
  • 106
5
votes
1 answer

How to check VARCHAR(n) for well-formed XML before CAST/CONVERT

My company has a logging table containing a VARCHAR(N) column in which a string is placed that is supposed to be XML, but as it turns out it is not always well-formed. In order to perform analysis on the logging (to identify error trends, etc.), I…
gregsdennis
  • 7,218
  • 3
  • 38
  • 71
4
votes
1 answer

SQL Server 2008 find and replace elements in XML column

In the following table: CREATE TABLE [dbo].[GDB_ITEMS]( [ObjectID] [int] NOT NULL, [UUID] [uniqueidentifier] NOT NULL, [Type] [uniqueidentifier] NOT NULL, [Name] [nvarchar](226) NULL, [PhysicalName] [nvarchar](226) NULL, …
tpcolson
  • 716
  • 1
  • 11
  • 27
4
votes
2 answers

SQL Server XQuery in one line?

DECLARE @myXml XML SET @myXml = CONVERT(xml, '', 1) SELECT s.value('@key', 'VARCHAR(8000)') AS myKey from @myXml.nodes('/a') t(s) answer : which is fine. I want to do it without declaring the @myXml variable. Something…
Royi Namir
  • 144,742
  • 138
  • 468
  • 792
4
votes
1 answer

Update XML Node with Conversion in SQL Server 2005

I have a column that contains XML data, but is TEXT type and not XML type. (I have to leave it like this for another reason). Basically i need to cast it to NText first and then XML. The only problem is my current format that works for selecting the…
the sandman
  • 1,019
  • 3
  • 12
  • 32
4
votes
3 answers

Search for multiple values in xml column in SQL

This is my table BasketId(int) BasketName(varchar) BasketFruits(xml) 1 Gold 123456 2 Silver …
nav
  • 127
  • 1
  • 2
  • 10
4
votes
2 answers

In an SQL XQuery result set, is there a way to make the "value" operator return null on missing elements?

When getting values out of xml attributes, using the xquery value operator, missing attributes are returned as null. Is there a way to do the same thing without resorting to CASE WHEN N.exists('path')=0 THEN NULL ELSE n.value('path') END? This…
lambacck
  • 9,768
  • 3
  • 34
  • 46
4
votes
1 answer

SQL Server Query XML in Nvarchar(max) Field?

I have XML stored in an nvarchar(max) field. I realize there is an XML data type, but in this case it is not stored that way. Let's say the XML is structured like the following: aj …
aherrick
  • 19,799
  • 33
  • 112
  • 188
4
votes
3 answers

Retrieve all XML elements with the same prefix in SQL Server

I have an XML file in a format similar to: 100 200 300 400 I need to write a query that will get all of the element values that start with Field. So given the…
pagspi
  • 53
  • 5
4
votes
2 answers

XQuery adding or replacing attribute in single SQL update command

I have a Table with an XML column, I want to update the xml to insert attribute or to change the attribute value if the attribute already exists. Let's say the starting xml is: < d /> Inserting: UPDATE Table set XmlCol.modify('insert attribute att…
Hans
  • 41
  • 1
  • 2
1
2
3
…
30 31