Thank you, I ve just found my mistakes, the solution would be:

SELECT TempXML.Node.value('(libelle)[1]', 'varchar(50)') AS mylibelle,

TempXML2.Node2.value('name[1]', 'varchar(15)') AS myname

FROM @doc.nodes('//produit') TempXML (Node)

CROSS APPLY...

Thanks ChrisM@home for the reply, but tab2 doesn't have 'city' column.

as mentioned in the step 2, I want to join, on city, records matched in step1 with those didnt match...

Hi thanks for the reply.

here is more details:

tab1 (postalCode, city,....)

tab2 (postalCode, .....)

step1: join tab1 with tab2 on postalCode and get all columns from tab1 and tab2 (simple join)

step2: take unmatched...

thanks, here is my final version, it's working perfectly !

DECLARE @wholeFileName varchar(200)

DECLARE @PathFileName varchar(200)

DECLARE @FName varchar(30)

DECLARE @RowCnt int

DECLARE @MaxRows int

SET @PathFileName ='\\my_path\'

DECLARE @tabfileNames TABLE...

thanks, here is my final version, it's working perfectly !

DECLARE @wholeFileName varchar(200)

DECLARE @PathFileName varchar(200)

DECLARE @FName varchar(30)

DECLARE @RowCnt int

DECLARE @MaxRows int

SET @PathFileName ='\\my_path\'

DECLARE @tabfileNames TABLE...

Thank you very much, I really appreciate it

Lowell, Yes if you have an example will help for sure. Thanks

Thanks again Lowell

actually, I need only XML files name to pass them to a script which shred them into sql server.

I already wrote a dynamic script and it works...

Merci beaucoup Lowell !

do you have any suggestion how can I loop to pass names one by name? should I use a cursor or is there any better way?

thanks...