Great, here's another way

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1),

OrderedData AS (

SELECT jc.WBSNumber,

jst.EquipmentNo,

...

This is the query from above without the WITH XMLNAMESPACES clause

SELECT jc.WBSNumber AS "site_code",

(SELECT jst.EquipmentNo "equipment_code",

...

I'm not sure if you can do this without generating extra namespace definitions, there's a Connect item for it here

http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1)

SELECT...

Can you post the DDL and DML to create the sample data.

I think rearraging the calculation in dbo.fnPaymentCalc improves things a bit

Change

SELECT @payment = @loan_amt*((@calc_rate*POWER((1+@calc_rate),@periods))/(POWER(1+@calc_rate,@periods)-1))

to

SELECT @payment = @loan_amt*@calc_rate + @loan_amt*@calc_rate/(POWER((1+@calc_rate),@periods)-1)

SELECT e.emp_no AS "@emp_no",

e.ename AS "@ename",

e.manager AS "@manager",

(SELECT 'item'...

MidBar (7/10/2011)

---

Great Champ!!!! Excellent work.

BY giving the namespace in individual item, it worked as desired.

However, I am still not sure what's the purpose of this "*." in this clause...

Any feedback?

Did either of the solutions work for you?

MidBar (7/8/2011)

---

Excellent!!!

This works with single element in body. Of course one cannot write n number of outer apply for each element in body. I have tested following and its...

This should work

select b.value('.','varchar(10)') as Item,

h.value('.','varchar(50)') as [To]

from #temp

outer apply item_xml.nodes('

declare default element namespace "http://tempuri.org/";

declare namespace s="http://www.w3.org/2003/05/soap-envelope";

declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts";

/s:Envelope/s:Body/ExecuteNewPolicy/NewFacts/a:NewOrders/a:NewOrder/a:item') as body(b)

outer apply item_xml.nodes('

declare...

with xmlnamespaces ( default 'http://tempuri.org/',

'http://www.w3.org/2003/05/soap-envelope' as s,

...

Uncomment /*NOT*/ for the other dataset

SELECT RU,SECTOR,ENT

FROM WBU w1

WHERE /*NOT*/ EXISTS(SELECT * FROM WBU w2 WHERE w2.SECTOR='CORE' AND w2.RU=w1.RU)

Thanks for the feedback.

This should give you what you want. I've added a couple of extra rows of test data.

SET DATEFORMAT MDY

DECLARE @EmployeeDaily TABLE(FileDate DATETIME, EmployeeId INT,EmployeeName VARCHAR(30),ManagerId INT)

INSERT INTO @EmployeeDaily(FileDate,EmployeeId,EmployeeName,ManagerId)

SELECT '5/1/2011' ,101,...

Have a look here

http://www.sqlservercentral.com/articles/T-SQL/71550/