I don't think concatenation with an 'order by' is reliable, see this link

http://support.microsoft.com/default.aspx?scid=287515

As an alternative you can use 'for xml', something like this

CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Output VARCHAR(8000)...

There's several techniques and timings described here

http://www.sommarskog.se/arrays-in-sql-2005.html

Not sure what you mean, maybe this?

with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)

select

r.value('(n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField[@FieldName="ActivatedDate"]/n1:FieldValue)[1]/@DateTimeValue','varchar(30)') as ActivatedDate,

r.value('(n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField[@FieldName="ConsultantPromDt"]/n1:FieldValue)[1]/@DateTimeValue','varchar(30)') as ConsultantPromDt,

r.value('(n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField[@FieldName="ContractReceive"]/n1:FieldValue)[1]/@DateTimeValue','varchar(30)') as ContractReceive

from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity') as x(r);

with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)

select r.value('@FieldName','varchar(30)') as FieldName,

r.value('n1:FieldValue[1]/@DateTimeValue','varchar(30)') as FieldValue

from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity/n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField') as x(r);

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:IsWithinCityLimits/n1:FieldValue)[1]','varchar(30)') as IsWithinCityLimits

In your XML, AddressLine1 isn't a sub-element of AdditionalFields

Try this

with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)

select r.value('n1:ExternalReference[1]','int') as ExternalReference,

r.value('n1:ChangeType[1]','int') as ChangeType,

r.value('n1:Salutation[1]/@CodeID','varchar(30)') as Salutation,

--r.value('(n1:Salutation/n1:CodeGuid)[1]/@DecimalValue','varchar(30)') as SalutationCodeGuid,

r.value('n1:FirstName[1]','varchar(30)') as FirstName,

r.value('n1referredName[1]','varchar(30)') as PreferredName,

r.value('n1:LastName[1]','varchar(30)') as LastName,

r.value('n1:AuditNumber[1]/@DecimalValue','varchar(30)') as AuditNumber,

r.value('n1:BusinessEntityType[1]/@CodeID','varchar(30)') as BusinessEntityType,

r.value('n1:Culture[1]/@CultureID','varchar(30)') as Culture,

r.value('n1:Currency[1]/@CurrencyID','varchar(30)') as Currency,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:AdditionalFields/n1:AddressLine1)[1]','varchar(30)') as AddressLine1

from...

Here are a couple of queries against your XML. The tables you load the XML into will typically be dependent upon the 1:n relationships in the data.

declare @x xml

set @x='

......

SELECT r.value('local-name(.)','varchar(10)') AS name,

r.value('data(.)','varchar(50)') AS value

FROM Tablename

CROSS APPLY XML_DATA.nodes('/ROOT/ROWS/*') AS x(r)

See if this helps

SELECT o.opid,o.enid,o.milestone,o.daysopen,

e.orgsize,g.days,

CASE WHEN o.daysopen > g.days THEN 'Not Comply' ELSE 'Comply' END

FROM #op_opportunity...

SELECT TOP 1 @TestDate = startdate from table1

Note that you would normally have an ORDER BY clause in this scenario

> Any idea how to insert more than 1 value without a loop?

Jeff,

Sorry, don't know that one.

Simply this

INSERT dbo.MyTable DEFAULT VALUES

I think this can be simplified, but should work

WITH ALevel (ConsultantID, AchieveLevel, AchieveTitle, PeriodEnddate) AS

(

SELECT ConsultantID, max(AchieveLevel), AchieveTitle, PeriodEndDate AS DirectReports

FROM Volume

WHERE ConsultantID = @ConsultantID

GROUP BY ConsultantID

,AchieveTitle

,PeriodEndDate

) ,

CTE AS...

WITH XMLNAMESPACES( 'http://com.btsws.schema.bc.order' AS "s")

select

cast(

contents.query('/s:data/s:main/s:row/s:order_id/text()') as nvarchar(1000)),

contents.value('(/s:data)[1]/@SequenceNumber','int')

from dirbts

order by id