You can get one row per parameter using this

with cte as (

select cast(Parameter as xml) as Parameter

from [dbo].[Catalog])

select r.value('Prompt[1]','varchar(10)') as ParameterName,

(select rv.value('.','varchar(10)') as...

with cte as (

select cast(Parameter as xml) as Parameter

from [dbo].[Catalog])

select r.value('Prompt[1]','varchar(10)') as ParameterName,

rv.value('.','varchar(10)') as DefaultValues,

rd.value('.','varchar(10)') as...

declare @x xml

set @x='..your xml here..'

select r.value('Prompt[1]','varchar(10)') as ParameterName,

rv.value('.','varchar(10)') as DefaultValues,

rd.value('.','varchar(10)') as Dependencies

from @x.nodes('/Parameters/Parameter') as x(r)

outer...

with cte as (

select CustomerNumber, PolicyNumber ,StartDate , Premium , EndDate,

count(*) over(partition by CustomerNumber) as cn

from mytable)

select sum(Premium) as [sum for all policies],

...

WITH CTE AS (

SELECT EmpNum,ClockInDate,ClockInTime,ClockOutTime,

ROW_NUMBER() OVER(PARTITION BY EmpNum,DATEPART (dw,ClockInDate) ORDER BY ClockInDate) AS rn

FROM @TimeClock)

SELECT EmpNum

...

Not sure how you can get one row per /root tag where there are

multiple tbl_Component and tbl_Insert child elements, however this may help

SELECT XMLTable.XMLColumn.value('tbl_Site[1]', 'VARCHAR(25)') AS tbl_Site,

XMLTable.XMLColumn.value('tbl_Applications[1]', 'VARCHAR(25)') AS...

You'll need a calendar table with month start and end dates

if object_id('tempdb..#Calendar') is not null

drop table #Calendar

Create table #Calendar(dtStart DateTime,dtEnd as dateadd(day,-1,dateadd(month,1,dtStart)))

insert...

Select sum (case when (CPP.Service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as...

Something like this

Select sum (case when CPP.Service like 'ci%' then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [ci_sum],

...

WITH VersionsRn AS (

SELECT *,ROW_NUMBER() OVER(PARTITION BY Item_Ref ORDER BY Ref DESC) AS Rn

FROM Versions)

SELECT i.*,v.*

FROM Items i

INNER JOIN VersionsRn v ON v.Item_Ref=i.Ref AND v.Rn=1

select r.value('number[1]','decimal(10,3)') as Sample2Value

from SampleXML

cross apply Data.nodes('/wddxPacket/data/struct/var/struct/var[@name="Sample2"]') as x(r)

I think

sys.all_objects.name = 'dbo.tblEXTRACT_RecoveriesClient'

should be

sys.all_objects.name = 'tblEXTRACT_RecoveriesClient'

change

sys.tables.name = 'Table 2'

to

sys.all_objects.name = 'Table 2' and sys.all_objects.type='U'

SELECT OBJECT_NAME(@@PROCID)

Maybe this?

select a.FEILD1,a.LEVEL1,a.SCORE,a.Rank,

(select b.Rank as "text()"

from mytable b

...