.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

XQUERY using Cross Apply and Outer Apply

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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(

View Complete Post

More Related Resource Links

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb


table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! CREATE FUNCTION fnt_FullTextSearch ( @s NVARCHAR(4000) ) RETURNS TABLE AS RETURN ( SELECT [key], [rank] FROM FREETEXTTABLE(table1, *, @s) ) DECLARE @terms TABLE ( term VARCHAR(MAX) ) INSERT INTO @terms VALUES ( 'flu' ) INSERT INTO @terms VALUES ( 'acid' ) --The inline function "..." cannot take correlated parameters or subqueries -- because it uses a full-text operator. SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY fnt_FullTextSearch(term) ft --syntax error on term SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY FREETEXTTABLE(table1, *, term)

problem with cross apply query

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you. SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;

CROSS APPLY doesn't work in some databases

I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?Here is the simplest testcase I can come up with:select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) bIf this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails withMsg 102, Level 15, State 1, Line 3Incorrect synt

Why does the engine ignore my Cross Apply?

I'm a beginner attempting to understand cross apply. Suppose I have a Numbers table with only one column: DECLARE @Nums Table(Num int) INSERT INTO @Nums (Num) Values (5) INSERT INTO @Nums (Num) Values (6) INSERT INTO @Nums (Num) Values (7) The following produces two columns of output (in SSMS Express 2005). SELECT  * FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable Now let's change the SELECT * to  SELECT Num (but I would expect the same result since that's the only column in the table). SELECT  Num FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable But instead I only get one column of output - the engine simply ignores my Cross Apply clause. Why?    After all, I'm told that using SELECT * is bad practice because I should name my columns explicitly. But in this case I'm getting punished for naming my columns explicitly?  I must be doing something wrong, but I don't know what it is.

Using CROSS APPLY in a data source query causes ARITHABORT error


I have an issue where the following query as an OLEDB data source throws the following error:

SELECT XmlNode.value('SomeValue[1]', 'varchar(10)') as SomeValue

FROM (SELECT CAST('<TestXML><SomeValue>123</SomeValue></TestXML>' AS XML

Outer Apply, within an Outer Apply


Good morning, I have a from clause that uses an outer apply function to get the most recent sales order from a job order, how ever the sales order could be against one of several items. This is my current statement: I would appriciate any help in fixing this error.







group by clause in cross apply



I am using cross apply against 2 huge tables and I am trying to show the number of customers customers in 3 of the countries US, Canada, UK. When I use my cross apply how do I show the count of customers in the resultset. I have to do a cross apply becoz I do not have fields that have unique data that I can join on.


Product UK Customer US Customer CA Customer

A          1000              2000             3000

B           0                  500               2000

C         1200              2250              0

D         3400              5650              2390  



Cross Apply



  I have created the following funcation :

 ALTER FUNCTION [dbo].[fnTopNByCategory]

(@TopN int, @CategoryID int)


Cross Apply


Foundations: Easily Apply Transactions To Services


Managing state and error recovery using transactions is the topic of this month's installment of Foundations.

Juval Lowy

MSDN Magazine January 2009

Unit Testing: Apply Test-Driven Development to your Database Projects


Jamie Laflen extols the benefits of TDD when applied to database development-and supplies some useful techniques along the way.

Jamie Laflen

MSDN Magazine Launch 2008

Apply custom.master to "Site Settings" page?


I'm using a custom.master page, which is applied to almost all pages available to READ users.

However, I'd like to apply it to the Search, Site Settings, and View All Site Content pages.

I'm not sure where this would be applied.

Guidance much appreciated. Thanks!

.NET4, DataGrid doesn't apply EditingElementStyle with ValidatesOnExceptions when catching an excep

Hello everyone, I'm using WPF and the .NET 4 framework (4.3.30319), on the Windows 7 32-bit OS. I wanted to keep things simple with validation and my property setter just throws an ArgumentException when the data is not valid, instead of implementing a ValidationRule class. While this worked with a ListBox, it seems it doesn't with a DataGrid. Below is a simple example to reproduce the problem, check the XAML and C# code related to the 2nd column. The datagridTBStyle is meant to apply a specific style when an error occurs (since control templates are out of the question with DataGrid), but what I see is: it applies the correct style when editing the cell (blue text) it doesn't apply the part that is supposed to trigger on Validation.HasError (red on yellow background + tooltip) Without this style, it doesn't apply a red border around the cell as it should according to the documentation. However, it does put a part of red exclamation mark in the row header when an error occurs, and it catches the exception. Am I doing something wrong, or is this actually a bug? Link to the documentation. <Window x:Class="DataGridTest.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Test" Height="200" Width="350&q

how to apply validation controls on Gridview which is created dynamically

 hi, i created a gridview with two columns and 8 rows dynamically. All the rows are empty and editable at runtime. I am giving column names for this grid from some other .aspx page by passing column names using querystring.Because i am using this grid for three different pairs of column names.  i would like to know how to apply Required Field Validator, Regular Expression Validator  and Range Validator on Gridview textboxes. So that by using those validators i want to validate user inputs. And i would also like to know how to add rows at runtime for this grid. I am using ASP.net 2.0 and C#.net for this.(Visual Studio 2005) The code i am using to create gridview is below. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="LoadData.aspx.cs" Inherits="LoadData" %>  <form id="form1" runat="server">  <div> <asp:GridView ID="GrdDynamic" runat="server" AutoGenerateColumns="False" ForeColor="#333333" BorderColor="GradientInactiveCaption" BorderStyle="Solid" style="z-index: 108; left: 24px; position: absolute; top: 13px">  <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  <RowStyle BackColor="#EFF3FB"

how to apply css to custom web part?

I've created a custom web part in visual studio 2005. i want to apply style sheet to this web part? how do i do this? where should i add reference for css in custom code?

How to I apply an XAML declared animation to multiple controls dynamically?

Hello, How to I apply an XAML declared animation to multiple controls dynamically? Here is the XAML declared animation: <Window.Resources> <Storyboard x:Key="TextBlock_SB"> <DoubleAnimation Storyboard.TargetName="TextBlock_1" Storyboard.TargetProperty="(TextBlock.Opacity)" From="1.0" To="0.75" Duration="0:0:0.5" AutoReverse="True" RepeatBehavior="Forever" /> </Storyboard> Here is the code that starts the animation for TextBlock_1: (this.Resources["TextBlock_SB"] as Storyboard).Begin(); I want to apply the same storyboard/animation to TextBlock_2 and TextBlock_3 and perhaps others.  How do I do that without defining more XAML storyboards? Regards, Robert  
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