.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

help on Xquery

Posted By:      Posted Date: October 26, 2010    Points: 0   Category :Sql Server
In the below TestXML structure (which is stored in a temp table #Temp1)
<Slave AutoID="13832" Version="49" CheckID="check1" />
<Slave AutoID="13832" Version="49" CheckID="check2" />
        <Slave AutoID="13832" Version="49" CheckID="check4" />

        <Slave AutoID="13832" Version="49" CheckID="check9000" />
i want to use xquery to parse through the above xml and dump all the values of CheckID attribute to a temp table at once.
I tried 

declare @x xml
declare @t int
set @t=1
select @x = TestXML from #Temp1

select @x.query('/Master/Slave[position()=sql:variable("@t")]/@CheckID) from #TEMP1 <-- Error--> XQuery [query()]: Attribute may not appear outside of an element

select @x.value('/Master[1]/Slave[position()=sql:variable("@t")]/@CheckID','nvarchar(200)') from #TEMP1  

View Complete Post

More Related Resource Links

Data Points: Using XQuery, New Large DataTypes, and More


SQL Server 2005 introduces a lot of new features, but it also enhances the popular and oft-used Transact-SQL (T-SQL) language. Changes include the introduction of new datatypes to store large values using the MAX indicator, the integration of enhanced XML querying and data modification with XQuery, and the new XML datatype.

John Papa

MSDN Magazine March 2006

How to use a dynamic XPath with XQuery?

HI, I've a problem with dynamic XPath-Expressions used in Xquery functions as shown below.DECLARE @XML1 xml DECLARE @XPath as varchar(200) SET @XML1='<Root> <Device> <Inspection> <Status>OK</Status> </Inspection> </Device> </Root>' -- This returns the correct results SELECT @XML1.query('/Root[1]/Device[1]/Inspection[1]/Status[1]') SELECT @XML1.value('/Root[1]/Device[1]/Inspection[1]/Status[1]','varchar(5)') -- This returns the Xpath-Expression itself or a part of it and not the expected results SET @XPath='/Root[1]/Device[1]/Inspection[1]/Status[1]' SELECT @XML1.query('sql:variable("@XPath")') SELECT @XML1.value('sql:variable("@XPath")','varchar(5)')What's wrong with the use of "sql:variable"Thanks in AdvanceRaimund

How to use 'Like' operator in XQuery?

Hi All, I've a huge XML. Following is the layout: <Root>           <Request ID="12160884">                    <Command ID="1" Item="Start_Loading" Error="" />                    <Command ID="2" Item="GetData()" Error="Unknown group 'Data'" />                    <Command ID="3" Item="End_Loading" Error="" />           </Request>           <Request ID="12161224">                    <Command ID="1" Item="Start_Loading" Error="" />                    <Command ID="2" Item="GetCompanyInfo()" Error="" />               &

XQUERY using Cross Apply and Outer Apply

Trying to shred XML into relational table rows and can do it for one set of node values .. having difficulty creating a pair of columns. The relevant part of the XML looks like this ... <ns:Coverage>      <ns:CoverageCd>cvalue1</ns:CoverageCd>      <ns:Form>           <ns:FormNumber>fvalue1</ns:FormNumber>      </ns:Form> </ns:Coverage> I can successfully navigate through the XML and pull back a set of rows for all values of CoverageCd. Now I'm trying to add a column to each output row for the FormNumber value associated with the CoverageCd value. However; FormNumber is not always present. I found an example where using 'Outer Apply' can account for that, but it's not working for me. Here's my XQuery: WITH XMLNAMESPACES('http://www.mycompanyname.com/ACORD1.11.0/Policy_1.0/xml' AS "ns") select cov.i.value('.', 'varchar(30)')   [Coverage],          form.i.value('.', 'varchar(30)')  [FormNumber] from  dbo.mytablename        cross apply XMLcolumn.nodes('.//ns:Coverage/ns:CoverageCd') as cov(i)        outer apply cov.i.nodes('.//ns:Coverage/ns:Form/ns:FormNumber) as form(

XQuery: Unable to resolve sql:variable('@variable').

Solution to problem in SQL 2005: http://beyondrelational.com/forums/p/4289/7997.aspx USE tempdb GO -- If table exists, drop it IF OBJECT_ID('Tbl','U') IS NOT NULL BEGIN DROP TABLE tbl END GO -- Create the source table CREATE TABLE Tbl (col XML) INSERT INTO Tbl (col) SELECT ' <page height="782" width="620" id="page1" number="5"> <layout> <region id="r1" x="0" y="0" height="782" width="620"> <img src="../../Images/page11.swf" /> </region> </layout> </page>' -- Local variables DECLARE @page1 VARCHAR(20), @sQuery NVARCHAR(MAX), @insertXML NVARCHAR(MAX) SET @page1 = 'page1' SET @insertXML =' <hotspot id="C0D7AE7C5807FCA934C02E8FE1765031E4B4FC3B" x1="38.45" width="0" y1="53.6" height="0" FileName="" pageRF="" roleId="1" FilePath="" externalLinkURL="http://...._hsw_hswsnake.pdf" title="A visual ...Works." index=""> <role id="34" action=""/> </hotspot>' DECLARE @sql NVARCHAR(MAX) SET @sql = 'UPDATE Tbl SET col.modify('' insert ' + @insertXML + ' into (//page[@id=sql:variable("@page1"

Using Xquery with XML and vb.net to create a search facility

Hi Guys Newbie usign vb.net and vs2005, with .net.2.0 Need some help, i have a page with a datagrid, it is using an xml file as the datasource, it has paging and this works fine, what i now want to do is query the xml file based on the data the user enters into a text box, i then want to run the query using xquery and return the result set to a label. I have the code below so far which is searching my xml file using a fixed hard coded search parameter and it works but im stuck on how to implement the textbox text and search. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:Label ID="Label1" runat="server" Visible="False"></asp:Label><br /> <br /> <asp:TextBox ID="searchtxt" runat="server"></asp:TextBox>&nbsp;<asp:Button ID="search" runat="server" Text="Search" /><br /> <br /> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="15"> </a

XQuery in TSQL

Why can i use it like this:   SET @xml.modify(' delete if(/employees/employee/name[sql:variable("@index")][contains(.,@type)]) then (/employees/employee/name[sql:variable("@index")]/@type) else() ')   but can not use it like this:   SET @xml.modify(' insert attribute type {string("Normal")} as first into (/employees/employee/name)[sql:variable("@index")] ')  

Count Value In XML Field Via XQuery

I have an XML field in SQL Server 2008. Within the XML is a node entitiled "Key".  There are some XML fields that will have the same value of 'Key'.  I want to count how many duplicates per 'Key'.  For example, if I query :  select   messagebody.query('/msg/body/Color/Key') from #t then the result might be: <Key>735110040</Key> <Key>0115220</Key> <Key>735110040</Key> I need to get the following output: 735110040, 2 0115220, 1 If I incorporate the traditional a count/group by with XML then I can't use group by as normal or I get "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause." : select   messagebody.query('/msg/body/RetrieveAccount/Key'), count(('/msg/body/RetrieveAccount/Key'))from #t  group by count(('/msg/body/RetrieveAccount/Key'))  I've tried variations on the xquery count() function but I keep getting something wrong.  Does anyone know what the correct syntax for this scenario would be? Thanks, cj

Recursive XQuery Order by


I got an recursive XML in my database generated by my stored functions. Simple example:


How to use variable in XQuery


I want to use a local sql variable in XQuery.

Following is my table structure with columns and data type.










OrderDetail column contains XML in the following form.

  <Book BookID="033ba62a-3bbe-48cc-ad15-c3f28ff86306" Title="XYZ" Price="120" />
  <Book BookID="805BC2BE-A91E-4E0E-A99E-C290D2EEEFF6" Title="XYZ" Price="120" />

I want to select Orders (rows) which have BookID = "033ba62a-3bbe-48cc-ad15-c3f28ff86306". I am able to achive it through the following query.

Select * From Orders Where OrderDetail.exist('//Book[@BookID = "033ba62a-3bbe-48cc-ad15-c3f28ff86306"]') = 1

Now the issue is I want to use an sql local variable (@BookID) instead of the hardcoded BookID.

So How I can use a sql variable in a XQuery.

Stored Procedures using XQuery to insert XML data into tables


If I have the following XML being passed as a parameter into a stored procedure.


<name>My Bookstore</name><br/>
<location>New York</location><br/>
 <title lang="eng"&g

Bulk update of columns using xquery and xml parameter


 I have an application that updates more than 150 columns in the database. I was looking for an alternative to using
 an SP with a huge parameter array (which would be quite stupid) and decided to use a method that i found on MSDN, that is ,
 passing the data in an xml document like this :
 If it comes to inserting data into a table , this is very helpful, i can do something like this :
 DECLARE @result TABLE (xml_data XML )
 DECLARE @resultTable TABLE ( CODE varchar(20) , TOPC_XML XML )
 INSERT @resultTable
 SELECT   x.y.value('local-name(.)', 'VARCHAR(30)' ), 
 FROM @result 
 CROSS APPLY xml_data.nodes('/DATA/*') x (y)
But the problem is that along with insertion , I also have to update columns in the destination table. Is there some way that i can sort of iterate through the xml and create a update statement ? Or am i going down the wrong path? This method also poses the problem of data types. How can i preserve the data types ? One way is to send them along in the xml like this :


how to create a web site using c#(not used asp.net) ,xml,xsl,xquery ,xpath ,jquery


how to create a web site using c#(not used asp.net) ,xml,xsl,xquery ,xpath ,jquery



Hello Friends!

I admit, i'm new to XQuery, and have found it to be of good use when serializing objects to be persisted to Sql Server. I am having an issue understanding the basic syntax of the query (below).

DECLARE @testXmlString   XML

SET @testXmlString =

SELECT Employee.[name].value ('./name', 'varchar(80)') AS [Name]
  FROM @testXmlString.nodes ('/Root/employee') AS Employee (employee)

DECLARE @testXmlString   XML
SET @testXmlString =

SQL Server 2005 with Xquery


I have a field that contains XML data from DTS packages. I need to filter down the packages in the XML to ones that were only created by Excel.

Here's a chunk of the XML that I'm trying to get. There are a number of these sections contained in the XML root but I trimmed it down to just one.

<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
 <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
 <DTS:Property DTS:Name="ObjectName">BGNDER1.DST_USER</DTS:Property>
 <DTS:Property DTS:Name="DTSID">{7FCF0E05-93D3-43ED-9FBB-001FFBB2C76B}</DTS:Property>
 <DTS:Property DTS:Name="Description" />
 <DTS:Property DTS:Name="CreationName">EXCEL</DTS:Property>
   <DTS:Property DTS:Name="Retain">0</DTS:Property>
   <DTS:Password DTS:Name="Password" Sensitive="1" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAu770DM8V2U+apqMxnqpa9gAAAAAIAAAARABUAFMAAAADZgAAqAAAABAAAACiI+bvYVuBEvLxnjJmlPl0AAAAAASAAACgAAAAEAAAAIK9G+8jvT5iZEdW6cMLu/MIAAAA96u8pvlHcBoUAAAADB07UGKGy2J6cq36Q/3c+IYQlMM</DTS:Password>
   <DTS:Property DTS:Name="ConnectionString">Data Source

XML Parsing with Xquery returning null records


I've never posted to these forums and I know this is a "common" problem, but I'm struggling big time on it.  


I've recently begun receiving xml files and ran into a small problem that seems syntax related, but I simply can't find where it is.  Below is the xml file and sample code of what I'm doing. I cut up the xml data, it's far more complex then what I'm presenting below.  I think it has something to do what how I'm address eventId but am struggling with figuring it out.  My preference is to stay out of SSIS if at all possible on this project. 

I'm getting rows to return, but they are all null when there is data in the file. 

If you see something with what I'm doing (and replied), I'd be greatly thankful. 

<?xml version="1.0" encoding="UTF-8"?> 
  <pitchMedia eventId="243"> 
    <media type="FLASH" url="xx.html"/> 

What is the best way to compute the value of XQuery position()?


This issue arises from a current limitation in the usage of position()

to within a predicate in SQL Server 2005 (if I interpreted the earlier

discussion threads correctly). In my scenario, I need to shred a

sequence of XML elements (actually, menu selections in a call);

but need to include the position/sequence number for each

element as well, when saving them to a relational table.


After realizing (the hard way) that SQL Server 2005 XQuery presently

supports the XPath position() function only in a predicate, I've tried to

synthesize some relevant threads here into a working Adventure Works

sample query which returns the positon() of sequential elements.

But, before going too far, I wanted to confirm with the XQuery

cognoscenti that this is currently the best approach to get the

positions of sibling elements:


select top 100 pm.Name as ModelName,

loc.row.value('for $l in . return count(../*[. << $l]) + 1', 'int') as LocSeq,

loc.row.value('@LocationID', 'int') as LocationID,

loc.row.value('@LotSize', 'int') as LotSize,

stp.row.value('for $s in . return count(../*[. << $s]) + 1', 'int') as StepSeq,

stp.row.value('(text())[1]', 'varchar(50

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend