Cadavre (11/14/2011)

---

Mark-101232 (10/5/2011)

---

See if this helps

DECLARE @x XML

SET @x='<MessageDelivery version="B000">

<ReturnMessage id="3652789340">

<AdC ocean="PACCGL">4815044</AdC>

<MessageStatus code="100" time="2011-09-25 13:43:22">status ok</MessageStatus>

<MessageData>700A20000018C0375494400000</MessageData>

<Flags les="0" app="0" read="1" />

</ReturnMessage>

</MessageDelivery>

'

SELECT x.r.value('local-name(.)','VARCHAR(20)') AS Element,

...

See if this helps

DECLARE @x XML

SET @x='<p>

<name>

<sur></sur>

<fn>AAA</fn>

</name>

<name>

<sur></sur>

<fn>BBB</fn>

</name>

<name>

<sur></sur>

<fn>CCC</fn>

</name>

</p>

'

SELECT a.b.value('.','VARCHAR(20)')

FROM @x.nodes('/p/name/fn') AS a(b)

Your second insert needs to be a single SELECT

INSERT INTO TestResult(Total)

SELECT (SELECT CONVERT(VarChar,Count(*) + 1)

...

You can do this with a local server group in SSMS and a query such as this

SELECT @@VERSION,COUNT(*) As NumberOfDatabases FROM sys.databases

Have a look here

http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/63650/

Something like this, using ROW_NUMBER()

WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY ProjectID ORDER BY ODD DESC) AS rn

FROM tblxrefProjectMICAP)

SELECT *

FROM...

Try changing

SELECT

(SELECT n.address1 "Address/AddressLine"

, n.address2 "Address/AddressLine"

FROM customer..cruise_bookings cb

to

SELECT

(SELECT

(SELECT Address FROM (SELECT n.address1 UNION ALL n.address2) d(Address) FOR XML PATH('AddressLine'),TYPE)

FROM customer..cruise_bookings cb

Something like this?

DECLARE @t TABLE(Col VARCHAR(20))

INSERT INTO @t(Col)

SELECT '5a' UNION ALL

SELECT 'ab45' UNION ALL

SELECT '1230' UNION ALL

SELECT '10B' UNION ALL

SELECT 'BAC7'

SELECT Col

FROM @t

WHERE Col LIKE '[0-9]%[A-Z]%';

JonFox (10/31/2011)

---

There are several ways of handling these kinds of problems, and it's hard to provide specifics without more information on your table structures and so forth. However, here's a...

Jeff Moden (10/18/2011)

---

Mark-101232 (10/18/2011)

---

Using a tally table

WITH AllAlpha(Letter) AS (

SELECT CHAR(ASCII('A')+N)

FROM dbo.Tally

WHERE N BETWEEN 0 AND 25)

SELECT (SELECT c.Letter AS "text()"

...

SQLRNNR (10/18/2011)

---

Lowell (10/18/2011)

---

nice Mark! ,because my Tally Table starts at 1 and not Zero, for me it's missing the group that starts with 'A', but it was trivial to change...

Using a tally table

WITH AllAlpha(Letter) AS (

SELECT CHAR(ASCII('A')+N)

FROM dbo.Tally

WHERE N BETWEEN 0 AND 25)

SELECT (SELECT c.Letter AS "text()"

FROM AllAlpha...

A bit obscure but try this

SELECT MerchantID from #tempSort

ORDER BY MerchantID COLLATE SQL_EBCDIC037_CP1_CS_AS

WITH CTE AS (

SELECT Year, Quarter, Amount, DateCreated ,LastModified, CreateEmployee , LastModifiedEmployee,

ROW_NUMBER() OVER(PARTITION BY Year ORDER BY LastModified DESC) AS rn

FROM #SomeTable1) ...

Can you give a bit more detail for your expected results. If you're after the min and max per year then this should work.

SELECT Year,

...

See if this helps

DECLARE @x XML

SET @x='<MessageDelivery version="B000">

<ReturnMessage id="3652789340">

<AdC ocean="PACCGL">4815044</AdC>

<MessageStatus code="100" time="2011-09-25 13:43:22">status ok</MessageStatus>

<MessageData>700A20000018C0375494400000</MessageData>

<Flags les="0" app="0" read="1" />

</ReturnMessage>

</MessageDelivery>

'

SELECT x.r.value('local-name(.)','VARCHAR(20)') AS Element,

x.r.value('./text()[1]','VARCHAR(20)') AS...