Polkadot,

I'm back in action! I see a lot of activity but it doesn't seem to yet be resolved, so let me try to answer these questions first.

(1) dwain the...

You need to do the LEFT OUTER JOIN after the WITH clause of the original INSERT query.

Sorry but I'm a bit sick tonight so I may not be able to...

OK that makes sense.

You can do that in the original query by doing a LEFT OUTER JOIN with the country table ON the country name field. However, that's generally...

Drew, you said:

After that, you should ship it off to your reporting tool to do rest. You are going to have to pivot the results to get your counts based...

Hehe... you like that huh?

Lucky for you I had a little time on my hands this morning.:-)

Allow me to let you in on why the counts may or may not...

Now i want only one change....

as you can see that in @xml we have CITY which is NVARCHAR so i want to get ID of each in a table...like if...

You can try this:

BEGIN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO TESTCITY (

[CITY]

,[COUNTRYID]

,[Keywords]

,[DialCode]

,UserID

)

SELECT *, @userid

FROM OPENXML (@idoc,@chk , 1)

WITH ([CITY] [varchar](50) 'CITY',

[COUNTRYID] INT 'COUNTRYID',

[Keywords] [varchar](50) 'Keywords',

[DialCode] [varchar](50) 'DialCode')...

OPC sorta beat me to it but I was going to ask you to provide the exact text of the error message.

Also, some DDL and sample data along with expected...

ReginaR,

Can you also confirm that you've tried some of the suggestions posted earlier and why they are not working for you?

It's nice to hear when an original work is appreciated!

Actually, in most of my posts where I'm suggesting a solution, I usually put something together from scratch. There is...

nhutlu,

Having been in your shoes about 1 year ago, I think I'm qualified to respond to you.

First of all, you need to gain a conceptual understanding of what SQL can...

Polkadot,

I'm not getting the same counts as your results set (I think I'm looking at the latest), however my counts match a visual check of the input data.

Regardless, I think...

You might also want to post the results from the following:

SELECT CAST(0 AS DATETIME)

Number of columns in the output set will depend on the maximum of the counts of the IDs in your table. Note that you can't use a TABLE VARIABLE...

The way I see it, you have 3 choices:

1. Dynamic SQL if you need a variable number of columns

2. A PIVOT (I think) but the max. columns will be fixed

3....