This OP "really appreciates" help

What help did you expect? You posted a vague question. So you got vague answers. And you haven't even answered my...

Please note that this is an 2000...

Just asking for clarification: are you running SQL2000? (that's the forum you posted in).

If yes I think one of the few ways in 2K to do it is to have...

What have you tried so far?

We're here to help you, not to substitute you...

Also, please read and follow the first link in my signature on how to ask for help...

Would you mind providing a short example of one of your xml file so we have something to test our solution against? Would help (at least me...)

Please provide table def and (fake) sample data together with your expected result in a ready to use format to show what you're struggling with.

If you need assistance please have...

What is the reason behind the requirement to loop through the XML?

You could get a table based on the xml:

SELECT

Z.Itemnmbr,

Z.Node.value('PromotionCode[1]','varchar(30)') AS PromotionCode

FROM

(SELECT

w.value('Itemnmbr[1]','varchar(100)') AS Itemnmbr,

X.Y.query('.') AS Node

FROM...

You could start search BOL (BooksOnLine, the SQL Server help system) for "Service Broker, catalog views". The result will be a list of system views related to SB. One of...

Unfortunately, your source file is...

Hmmm, now we have the SQL for the result set. But how about the source data? 😉

Glad I could help 😀

One more thing to notice:

you shouldn't use that code within a WHERE clause.

Instead, convert the parameter you query against using something like

WHERE mycol > = DATEDIFF(ss,'19700101',@MinDate)

to...

Ok, here's a sample that might get you started:

DECLARE @tbl TABLE

(

val CHAR(1)

)

INSERT INTO @tbl

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT...

Looks like a job for DynamicCrossTab. See the related link in my signature.

Side note: If you'd like to see a coded sample based on your data please provide ready to...

I'm just guessing here but it seems like those are the seconds based on a start date (I don't know the startdate though, so just guessing...)

SELECT DATEADD(ss,1271782698,'19700101')

/*Result

2010-04-20 16:58:18.000

*/