I've got an xml snippet that I'm extracting into a row.
Sample:
Code:
<TABLEDATA>
  <TABLEROW>
    <ID1>{guid1}</ID1>
    <VALUE1>Some data 1</VALUE1>
    <ID2>{guid2}</ID2>
    <VALUE2>Some data 2</VALUE2>
    <ID3>{guid3}</ID3>
    <VALUE3>Some data 3</VALUE3>
    <ID4>{guid4}</ID4>
    <VALUE4>Some data 4</VALUE4>
    <ID5>{guid5}</ID5>
    <VALUE5>Some data 5</VALUE5>
  </TABLEROW>
</TABLEDATA>
I'm able to extract the data and get it into a row:
Code:
ID1       VALUE1         ID2       VALUE2         ID3       VALUE3         ID4       VALUE4         ID5       VALUE5
{guid1} Some data1  {guid2} Some data2   {guid3} Some data3  {guid4} Some data4   {guid5} Some data5
I'm trying to rotate the data so that all of the IDs are in one col, ID, and all the related Values are in one col, Values:
Code:
ID         VALUE
{guid1} Some data1  
{guid2} Some data2   
{guid3} Some data3  
{guid4} Some data4   
{guid5} Some data5
For some reason I'm having trouble wrapping my head around this.
All of the examples I've found so far unpivot around one col and the rest of the data is denormalized to the cols... but here I'm trying to unpivot across two cols. I tried a double unpivot (two unpivot clauses) and I ended up with a cartesian effect.... ugh... not what I need or want.

As soon as I get back onto the machine where I'm working off of, I'll post the xml and shredding and the unpivoting I've tried.

Restriction - this is being done in a view so dumping the data into a temp table of any kind is out of the question. I think I might be able to use a multi-statement table function though.... hmmm... any ideas?

-tg