>Here is an example of processing an XML in a stored procedure:
>
>
>declare @myxml as XML = N'
><TABLENAME>
> <Fld Name="Field1">ValueInField1</Fld>
> <Fld Name="Field2">ValueInField2</Fld>
> <Fld Name="Field3">ValueInField3</Fld>
></TABLENAME> '
>
>
>SELECT [Field].value('.','varchar(100)') as FldValue, [Field].value('@Name','varchar(20)') as FldName
> FROM @myxml.nodes('/TABLENAME/Fld') Fld25 ([Field])
>
>
>FldValue FldName
>ValueInField1 Field1
>ValueinFiedl2 Field2
>ValueInField3 Field3
>
>
>My question is, how can I SCAN through the results. I know that SQL Server does not have a SCAN command so I use it as a pseudo term. But basically I need to create (dynamically) a SQL INSERT based on the values in the XML and the only way I see it possible is by scanning the results (keep in mind that the number of elements in the email is a variable).
>Any suggestions?
There was a recent thread on this exact topic in MSDN forum (either in T-SQL or in XML forum). I'll search later if I saved the code.
If it's not broken, fix it until it is.
My Blog