I would have expected your function to look like this

CREATE function fn_getXML(@url nvarchar(max))

returns TABLE (field1 nvarchar(1000), field2 nvarchar(150), field3 nvarchar(max), field4 datetime, field5 nvarchar(50))

EXTERNAL NAME [XMLPuller].[XMLPuller.XmlFunctions].[getXML]

Try this

CREATE TABLE MyTable(SlotNumber INT, StartTime INT, Duration INT, Status INT);

INSERT INTO MyTable(SlotNumber, StartTime, Duration, Status)

SELECT 1, 0,30,0 UNION ALL

SELECT 2,30,20,0 UNION ALL

SELECT 3,50,20,1 UNION ALL

SELECT 4,70,20,0;

WITH CTE AS (

...

Dave Ballantyne (7/9/2010)

---

Chris Morris-439714 (7/9/2010)

---

Can't help but giggle reading that 🙂

Now, how often have you had to tell folks that there's no guaranteed order of results from a query unless...

select r.query('.')

from @xml.nodes('/U/N[@id="2" and @ type ="good"]') as x(r)

Using this sort of approach, you should be able to dynamically pivot the data

DECLARE @data XML

SET @data ='<ITEM><RESPONDENT_ID>2</RESPONDENT_ID><Business_Account_Name>ElectroDom</Business_Account_Name><Category>Director</Category></ITEM>'

SELECT r.value('local-name(.)','VARCHAR(100)') AS Name,

r.value('.','VARCHAR(100)') AS Value

FROM...

stuff 56271 (6/22/2010)

---

Hi again

I just wanted to let you know that I put the query I liked the most in this thread into a blog post.

http://blog.crowe.co.nz/blog/archive/2010/06/22/SQL-Server--TSQL-Return-startend-price-and-startend-date.aspx

The reason was that I...

Look up ROW_NUMBER() OVER... in BOL

Here's another way

SELECT LEFT(a.Del_No,5) AS Del_No,

SUM(b.L_Amt) AS L_Amt,

a.T_Amt,

a.A_Name

FROM Table_A...

WITH CTE AS (

SELECT a, b, COUNT(*) OVER(PARTITION BY a,b) AS cn

FROM dbo.t)

SELECT a,b

FROM CTE

WHERE cn>1;

Here's another way

DECLARE @s-2 VARCHAR(100)

SET @s-2='TESTRBTEST'

SELECT @s-2 = CASE WHEN CHARINDEX(SUBSTRING(@s,Number,1),@s) BETWEEN 1 AND Number-1 THEN STUFF(@s,Number,1,'') ELSE @s-2 END

FROM master.dbo.spt_values

WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'

ORDER BY Number...

Jeff Moden (6/8/2010)

---

paul_ramster (6/7/2010)

---

Thanks Jeff, I'll fix that in the production code too!

darn flies!

Ah... my apologies for being the messenger. There's more than one fly... here are...

cgreathouse (5/31/2010)

---

Hello

I'm trying to find a way to collapse a large table I'm working with. I'm noticing that there are a lot of rows that are very similar. ...

ColdCoffee (5/31/2010)

---

Hey there.. try this:

declare @mytable table

(

id int,

potype varchar(10)

)

insert into @mytable values (1,'AA')

insert into @mytable values (1,'BB')

insert into @mytable values (1,'CC')

SELECT p1.id,

...

Another one to try

WITH CTE1 AS (

SELECT Name ,Price ,Date,

ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATE) AS rn1,

...

cgreathouse (5/20/2010)

---

OK, trying again...

Here's some SQL to get everything setup

declare @table table(ID int,

...