xquery

Function to achieve "Element does not have this child"?

I want to retrieve the documents in a collection which satisfy that a given element does not have a specific child element. For example, from the two "documents" below, I want to select doc 2, since it has the element child, but doc 1, since it does not. Doc 1: <doc> <element> <child/> </element> </doc> Doc2: <doc> <element> ...

Getting location of XML document in XQuery

I'm using Oxygen XML to to work with an XML file that contains references to other XML files. These other XML files are stored in a subdirectory of the directory that contains the main XML file. Downloads/ directory1/ main.xml subdirectory1/ other1.xml other2.xml The locations of the other XML files are stored as relative ...

XML Query within SQL Server

I just starting to query XML within a SQL Server database. I am having trouble with the most basic query. Here is a simplified example. How do I return description? The SELECT statement below is what I am using, but it returns nothing. SELECT Incidents.IncidentXML.query ('data(/dsIncident/IncidentInformation/Description)') AS Descrip...

[SQL Server 2005]: XML query() works, value() requires singleton found xdt:untypedAtomic

I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods: WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.query('declare namespace s="http://blah...

How do I build XQuerys to include/exclude rows based on the presence of certain tags and attributes?

I have an auditing/logging system that uses raw XML to represent actions taken out by an application. I'd like to improve on this system greatly by using an XML column in a table in the application's SQL Server database. Each row in the table would contain one log entry and each entry should contain one or more tags that are used to de...

XQuery Insert without namespace SQL2008

<game xmlns="http://my.name.space" ></game> This is my root element. I've written a stored procedure to insert elements into it. To sum up the stored procedure, here's the SQL UPDATE ChessGame SET GameHistory.modify('insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/game)[0]') W...

Getting multiple records from xml column with value() in SQL Server

This SQL only returns the first Activity element. How do I select them all? If I remove the [1] in the query I get an error that "value() requires a singleton". DECLARE @myDoc xml SET @myDoc = '<Root> <Activities> <Activity>This is activity one</Activity> <Activity>This is activity two</Activ...

UDF which auto-switches between text and base64 xml attributes

My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML: declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @D...

C# What query language to specify in rule to pull out the two book nodes.

<?xml version="1.0" encoding="ISO-8859-1"?> <bookstore> <book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price> </book> <book category="CHILDREN"> <title lang="en">Harry Potter</title> ...

Parse into HashMap using Commons Digester

I need to parse xml into a HashMap where the 'key' is the concatenation of two elements attributes. The xml looks like : <map> <parent key='p1'><child key='c1'> value1</child></parent> <parent key='p2'><child key='c2'> value1</child></parent> </map> In the 1st entry of map, I want to put 'p1.c1'as the map key while 'value1' as the...

Outputting CDATA in XQuery

How would I, using XQuery, transform <author>John Smith</author> to <author><![CDATA[John Smith]]></author> ? Also, how would I transform <content>&lt;p&gt;&lt;em&gt;Hello&lt;/em&gt;&lt;/p&gt;</content> to <content><![CDATA[<p><em>Hello</em></p>]]></content> ? If it matters, I am using XSLPalette.app. ...

Retrieve first value with Xquery using a wildcard

In an XmlData column in SQL Server 2008 that has no schema assigned to it, how can I pull the first item at a particular node level? For example, I have: SELECT XmlData.value('//*/*[1]','NVARCHAR(6)') FROM table where XmlData.Exist('//*/*[1]') = 1 I assume this does not work because if there are multiple nodes with different names ...

SQL Server xml string parsing in varchar field

I have a varchar column in a table that is used to store xml data. Yeah I know there is an xml data type that I should be using, but I think this was set up before the xml data type was available so a varchar is what I have to use for now. :) The data stored looks similar to the following: <xml filename="100100_456_484351864768.zip" ...

How to get a particular attribute from XML element in SQL Server

I have something like the following XML in a column of a table: <?xml version="1.0" encoding="utf-8"?> <container> <param name="paramA" value="valueA" /> <param name="paramB" value="valueB" /> ... </container> I am trying to get the valueB part out of the XML via TSQL So far I am getting the right node, but now I can not figure...

How to replace attribute value of *all* matching elements with XQuery?

Hi, I'm trying without luck to create a modify() statement to change the value of an attribute in all elements that have that attribute value -- so far I can only get it to change the value in the first matched element. I created an example below of what I have so far, which I'm running in SQL Server 2005: DECLARE @x XML SELECT @x = ' ...

How to change matched element text with XQuery?

Hi, I'm trying to piece together all the examples I've seen for finding elements and modifying them, but I haven't come up with something that works yet. I created an example below of what I have so far, which I'm running in SQL Server 2005. I'm trying to change the ItemID 4 to 999: DECLARE @x XML SELECT @x = ' <ValueCollection> <It...

Delphi and XQuery/XPath 2.0?

How do people use XQuery and/or XPath 2.0 from Delphi? I've got MSXML v6.0 working from Delphi, so any sufficiently documented COM-implementation will work. What's your favorite? ...

How do I Pivot on an XML column's attributes in T-SQL

I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example... FormEntryId | FormXML | DateCreated =========...

SQL Server 2005 Xquery namespaces

I'm trying to get some values out of an Xml Datatype. The data looks like: <Individual xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt; <FirstName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&gt;Lirria&lt;/FirstName&gt; <LastName xmlns="http://nswcc.org.au/BusinessEntiti...

SQL Server XML Declare Namespace

Hi All, I would like to have a few namespaces available by default when running an XQuery in SQL Server 2005, is this possible? For example, I would like to do the following: select * from TableWithXML where FieldWithXML.exist( declare namespace nsp="http://www.example.com/exampleNamespace"; nsp:root/nsp:childnode) = 1 Without having...