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


Top 5 Contributors of the Month
david stephan
Fauzul Azmi
Asad Ali
Post New Web Links

XQuery in TSQL

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
 
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")] ')  


View Complete Post


More Related Resource Links

How to Use Group By and Min with XQUERY in TSQL?

  

I have a XML data which is returned from a function as following:

 

<FinancialResult ID="100" PeriodStart="2009-04-01T00:00:00" />
<FinancialResult ID="99" PeriodStart="2008-04-01T00:00:00" />
<FinancialResult ID="102" PeriodStart="2007-04-01T00:00:00" />
<FinancialResult ID="101" PeriodStart="2007-01-01T00:00:00" />

 

I'd like to return only those runs that when grouped by Year of the periodStart column, it has the minimum IDs.

so I only want to return rows with IDs: 100, 99, 101 therefore 102 should be filtered (removed) because there is a record in the same period year (2007) and has a lower ID value.

Its TSQL equivalent would be like:

 

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


TSQL: Passing array/list/set to stored procedure (MS SQL Server)

  
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Approach using Regex in TSQL text manipulation

  
Hi there I have this following data: 0297144600-4799               0297485500-5599 The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach. But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid: 0297144600-4799      0297485500-5599      0297485600-5699 As you can see, I guess the first approach will the split by 1 blank space (" ") but due to number of space is unknown (varies) how do I take this approach then? Is there any method to find the space in between and shrink into 1 blank space (" "). I am exploring Regex not sure how to do this and what I can think of is that if the blank is space can somehow reduce from xxx to 1 and I can handle this the rest. Thanks dewacorp.alliances

Looking for TSQL statement that returns customers with greatest number of order in descending order

  
What would be an efficient TSQL SELECT statement that joins customer and order tables, counts the orders for each customer (group by) and returns a result for all customers with at least one order, and also in descending order from customers with the most orders to the least?

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"

XQeury 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")] ')

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

Tsql

  
this particular query takes the tototal execution time of 20 minutes and 52 seconds is there any posibilities to make this particular query faster? select   state_name,dist_name,tahsil_name,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm end ),0)as rm,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rf end ),0)as rf,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm+rf end ),0)as TotalRabbit,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm end ),0)as dm,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then df end ),0)as df,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm+df end ),0)as TotalDog,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em end ),0)as em,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then ef end ),0)as ef,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em+ef end ),0)as TotalElephant from state_master inner   join dist_Master ON Dist_Master.State_code=state_master.State_code INNER   JOIN Tahsil_Master ON Tahs

A tsql report with multiple counts and pivioted description attributes

  
Hello,   I have an assignment in which I am to prepare a report displayed in excel to return counts of various attributes in the database. However, the count is a bit challenging because it requires counting certain attributes in a pivot format illustrated below. The counts for each category by recruiter, rolled up by Division, within the specified time frame.   The tables that I would have to deal with are as follows:   User: Has the number of Accounts…user accounts with no Recruiter are unassigned Recruiter: Has the recruiter information LoginHistory: Has a record of all logins EForm: Has the various forms, for example: Release and Authorization, Provider Service Agreement EFormUserXref: You can filter it to return the Physican AppForms                                                                       Surgery                                      &n

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

TSQL Stored Procedure

  
Hi, I have a rather odd Stored Procedure I need to write, I was wondering if anyone could help me?   I have two tables which I need to basically match up and get information from.   Table: FormField - FormFieldID (PK) - Name   Table: FormFieldValues - FormFieldValue - FormFieldID - Value (Value of the FormField) - FormKey (This is to keep the form values together, for example Bob's DOB is 01/01/1975)   Example View   FormField.Name          FormFieldValue                   FormKey First_Name                 Bob                                    1 First_Name                 Jim                                     2 DOB                 &nbs

tsql

  
how would i convert a given number into an alphabet. suppose if user gives a number '1224' then the output should be 'one thousand two hundred and twenty four'. if anyone please reply.
Categories: 
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