The fields you mentioned may not be unique. I took out the elements that were confusing and the sample below along with result set that I would need....

Sorry for the confusion but that is not it. It needs to look like the results below.

/* result set

ID Name Value

873D5601-E800-40E6-B60E-0E39B663B290 ABC...

I want the result set you generated in your previous example. I have nothing in the xml that will work for unique id. I need to generate some...

I wanted to use that exact query but my problem is that there is nothing I can use to differentiate between the "orderdocumentrequest" elements. That is why I...

I like your option 2. I was trying that in the beginning with the query below but I could not figure out how to distinguish between each of...

It is not guaranteed that it will only be one space. So I tried the contains function and that should work.

Thanks Tim

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

...

I am looking to trim in the CROSS APPLY part. I getting XML that has a space after the value "loannumber" value in the NAME element.

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS...

Is there a trim function that I could use for the "loannumber"?

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

...

I thought the response earlier would have worked for what I wanted but looking into my xml it did not. I need to move the loannumber into the fields...

Thanks this is exactly what I needed.

Tim

I have figured it out. The dlls were support dlls of the .NET assemblies and they were not COM dlls. I ended up putting the dlls in...

That was the probelm. I did not even think of the query I was trying to execute was the problem. The piece of code has always worked. I...

Lutz,

Is there a way to use TOP 10 clause so that it returns 10 rows of the XML strings and not just the first 10 values from a the...

Lutz,

I did not want to build the Temp2 table but the way you are building the dynamic SQL with the case statement is pretty cool. I wanted to...

I just used FIELD1 and FIELD2 for the example. The _KEY attribute value can be anything. I would be interested in seeing the dynamic sql example.

Tim