Tom.Thomson (4/23/2010)

I looked at the thread Lutz, and to me it looks as if it's not lack of appreciatiuon it's standard American misuse...

I'm not sure if this will help performance... but it's worth a try 😉

DECLARE @var INT

SET @var = 100;

WITH XMLNAMESPACES ('http://schemas.somedomain.com/infopath/2003/appname' AS my)

SELECTc.value('../my:PECode[1]','varchar(20)') AS PECode

FROM@tbl tbl

CROSS APPLY

tbl.DocumentXML.nodes('/my:myFields/my:SelectedProductID[text()=sql:variable("@var")]') T(c)

WHERE

...

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...