Assuming you don't have an additional column that will number your rows starting with 1 you could either use ROW_NUMBER() or subtract the min(DATE) from your DATE column and add...

Looks like you're trying to build a calendar table.

You should add a column with datetime format to make it easier to recognize the date. You could do it by using...

A few more things to notice:

I'm not sure how the functions to_char and to_date are written, but I guess both can be replaced by the built-in function CONVERT(). The to_date...

Based on the limited information we have so far I recommend using a calendar table as the left part of an outer join.

Azzu (7/8/2010)

---

Hi

I have the following XML string

'<SampleXML>

<Colors>

<Color1>White</Color1>

<Color2>Blue</Color2>

<Color3>Black</Color3>

<Color4 Special="Light">Green</Color4>

<Color5>Red</Color5>

</Colors>

<Fruits>

<Fruits1>Apple</Fruits1>

<Fruits2>Pineapple</Fruits2>

<Fruits3>Grapes</Fruits3>

<Fruits4>Melon</Fruits4>

</Fruits>

</SampleXML>'

my question is how to knoiw whether the string is a valid xml or not. Preferably i'd like to do it...

(o_0) (7/7/2010)

---

Hi all,

I'm new to sp_xml_preparedocument

I'm running code below and is returning null values.

declare @idocint,

@xmlvarchar(200)

set @xml = '

<CD>

<TITLE>Empire Burlesque</TITLE>

<ARTIST>Bob Dylan</ARTIST>

<COUNTRY>USA</COUNTRY>

<COMPANY>Columbia</COMPANY>

<PRICE>10.90</PRICE>

<YEAR>1985</YEAR>

</CD>'

exec sp_xml_preparedocument @idoc output, @xml

select*

fromopenxml(@idoc, '/CD', 2)

with(titlevarchar(25),

artistvarchar(25),

countryvarchar(25),

companyvarchar(25),

pricedecimal,

yrchar(4))

is there something...

@Alban Lijo

You might want to have a look at the articles I pointed at in my previous post.

I guess the approach described in those articles will be "slightly faster" than...

I'm not sure whether it's a good idea or not to ask a question using the "sliced salami approach"...

Here's the related link with the other two questions regarding the very...

The main reason your first approach failed (at least as far I can see):

You have DATENAME(MONTH,LoginDate) in your GROUP BY clause.

This made the SUM() aggregation obsolete leading to the results...

Actually,

there is already an ongoing discussion here.

@vinod

opening an identical thread just because you don't seem to like the replies you get is not really a professional way...

As you might notice,...

There might be an easier way to do it but here's a solution that first extract the value and assign "P" as an table alias and "id" as the column...

It seems like you didn't bother to read and follow the article I pointed you at or at least follow the recommendation I made in my previous post.

It leaves the...

Table def in the format of CREATE TABLE FcHistory.dbo.tdwClaims () for all tables use in the query.

Fake but consistent sample...

How about

SELECT @xml_child_element = @xml_complete.query('/U/N[1]')

Instead of using the PIVOT approach I'd recommend you read the CrossTab article referenced in my signature.

The result of the CrossTab method is similar to the PIVOT approach with one...