You can use "contains" and "exist" for filtering. Not sure what you mean by error handling in this context.

XML is case sensitive

SELECT

x.location.value('declare namespace s="http://data.com.at"; s:YEARMANUFCATURE[1]', 'nvarchar(100)') AS yearmanuf,

x.location.value('declare namespace s="http://data.com.at"; s:MODEL[1]', 'nvarchar(100)') AS model,

x.location.value('declare namespace s="http://data.com.at"; s:MANUFCOUNTRY[1]', 'nvarchar(100)') AS countrymanuf

FROM @xmlvar.nodes('

declare namespace s="http://data.com.at";

/s:DATAROOT/s:VESSELS/s:VESSEL/s:VESSEL_INFO') AS...

The code returns the square of the number of rows in master.sys.columns, trivial I know but...

with cte as (

select DocID,cast(Doc as xml) as Doc

from mytable)

select Page.value('@page_number','int') as Page_number,

Redaction.value('Top[1]','int') as [Top],

Redaction.value('Left[1]','int') as...

andrewd.smith (4/9/2009)

---

I would make a couple of minor changes to Mark's query:

1) Use COUNT(a.fscore) rather than COUNT(*) when calculating cn in order to avoid counting rows with NULL fscore...

WITH CTE

AS

(

SELECT a.cust_key,

a.fscore,

ROW_NUMBER() OVER(PARTITION BY a.cust_key ORDER BY a.fscore ASC) AS rn,

...

You also seem to be using a couple of scalar function, can you post the code for thse as well.

Enterprise.dbo.fnGICMatrixFindActiveByVendor

GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor

Your solution is pretty much identical to the one I posted a few days ago.

If I understand correctly, try this

with cte as (

select dense_rank() over(order by ClientID) as id,

count(*) over(partition by ClientID) as DocCount,

...

From your sample data how do you know which row will appear in which tag? I can't see the "idx" attribute - my query uses this to group the rows.

Here's another way

WITH AgeRanges(AgeDesc,AgeMin,AgeMax) AS(

SELECT '0-10',0,10

UNION ALL

SELECT '11-20',11,20

UNION ALL

SELECT '21-30',21,30

UNION ALL

SELECT '31-40',31,40

UNION ALL

SELECT '41-50',41,50

UNION ALL

SELECT '51-60',51,60

UNION ALL

SELECT '61-70',61,70

UNION ALL

SELECT '71-80',71,80

UNION ALL

SELECT '81-90',81,90

UNION ALL

SELECT '91-100',91,100

UNION ALL

SELECT '100+',101,9999

UNION ALL

SELECT 'Not Entered',NULL,NULL),

PersonAges AS...

This is what is used to test and it appears to work. Can you post some of your sample data.

Can't see a simple way of doing this, but the query below should work

with cte as (

select id,

dense_rank() over(order by data.value('/doc[1]/@idx','int')) as dr,

...

Suggest you use FOR XML PATH with subqueries. Without proper DDL and sample data its difficult to give you anything concrete, but I would expect something like this

select PD.ID as...

I don't quite follow you here, you'll need to post some sample data and expected results