I have tried in vain to adapt your advice to this XML:

1 1 25 2 25 3 50

I am trying to change the XML to a resultset like this:

sourceposition fundposition percent 1 1 25 1 2 25 1 3 50

If there are multiple source positions, I need those to display as additional rows, as well. This is the query that I tried, and I just get NULL values.

SELECT x.value('(Field[@Name="sourceposition"]/@Value)[1]','VARCHAR(20)') AS sourceposition,
x.value('(Field[@Name="fundposition"]/@Value)[1]','VARCHAR(20)') AS fundposition,
x.value('(Field[@Name="percent"]/@Value)[1]','VARCHAR(20)') AS [percent]

FROM @x.nodes('/accountorder/source') d(x)

Thanks in advance for your help!

Try this query:

SELECT p.value('.','INT') AS SourcePosition, v.value('fundposition[1]','INT') AS fundposition, v.value('percent[1]','INT') AS FundPercent FROM @x.nodes('/accountorder/source') d(x) CROSS APPLY x.nodes('sourceposition') s(p) CROSS APPLY x.nodes('fund') AS f(v)

That worked awesome!!! If I wanted to incorporate that into a query of a table called psr with a field called psr_xml, how would I do that?

With what you provided, I will be able to write a stored proc to pull a specified record, declare the xml variable, and return this result. However, I would like to parse XML from multiple records, as well.

Thanks again!

does this help?

declare @t table (id int identity, data xml) insert into @t (data) select ' 1 1 25 2 25 '

insert into @t (data) select ' 1 1 25 3 50 '

SELECT p.value('.','INT') AS SourcePosition, v.value('fundposition[1]','INT') AS fundposition, v.value('percent[1]','INT') AS FundPercent FROM @t CROSS APPLY data.nodes('/accountorder/source') d(x) CROSS APPLY x.nodes('sourceposition') s(p) CROSS APPLY x.nodes('fund') AS f(v)