Performing an Unpivot - SQL Server 2012

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

Ungh... of course as soon as I pull it all up and get it ready to copy here... the [insert colorful language] laptop died... and it didn't bother to go to sleep or hibernate.... nooooo.... that would have made too much sense. So instead it decided to just up and quit, losing all of the work I had. Re-creating the XML was easy, but I lost the unpivot I had.

Maybe it was just as well... I think I found an alternate solution using a CTE and some union queries. feels like a kudge, but time is critical, and I just need to get it up and working. The rest of is isn't pretty anyways.

Here's the XML:

xml Code:

<MAPPEDVALUES>
  <ROW>
    <ID>99999999-9999-9999-9999-999999999999</ID>
    <ACCOUNTOFFICEID>1A692E94-3214-44B8-A658-F2927230BC25</ACCOUNTOFFICEID>
    <REVENUETYPEID>E94C5D99-45C3-4832-9394-E52D4639438C</REVENUETYPEID>
    <APPLICATIONTYPEID>ECA6E4A7-8B0B-4E80-BAFF-07A3559E4AD4</APPLICATIONTYPEID>
    <PAYMENTMETHODID>C24896E3-F243-45A1-BF2C-DA333C2B7F44</PAYMENTMETHODID>
    <ACCOUNTSYSTEMID>4B121C2C-CCE6-440D-894C-EA0DEF80D50B</ACCOUNTSYSTEMID>
    <SEGMENT02TABLEID>1BA6F27F-8454-4FC4-A830-3FDA8CD35D04</SEGMENT02TABLEID>
    <SEGMENT03ID>68F1B5E8-9BD5-4D9B-B2F0-12F1A7E347B6</SEGMENT03ID>
    <SEGMENT03TABLEID>3298E2CE-EF37-4681-AC12-050979B6C5F3</SEGMENT03TABLEID>
    <SEGMENT04ID>6300D932-0E86-4C2C-BE09-6791F5E41E55</SEGMENT04ID>
    <SEGMENT04TABLEID>AEBA8FD5-42B6-4D37-93EB-8916BEC1385A</SEGMENT04TABLEID>
    <SEGMENT05TABLEID>3EF5D061-5692-40F3-B8AD-1367A8C6FAD5</SEGMENT05TABLEID>
    <SEGMENT06ID>A044FAD4-D902-4D56-B197-FBD8877C9AE2</SEGMENT06ID>
    <SEGMENT06TABLEID>5B93DEF5-AAA7-482E-B8F7-169EE0DAA53D</SEGMENT06TABLEID>
    <ERRORMESSAGE></ERRORMESSAGE>
    <ERRORCODE>0</ERRORCODE>
    <HASHVALUE>0</HASHVALUE>
    <MD5HASHVALUE>087C5BE7-2DFD-A5B4-5822-288EC0A2DDC1</MD5HASHVALUE>
  </ROW>
</MAPPEDVALUES>

the fields I'm interested in are the Segment nodes, 02-06 ... there's a table ID for all of the segments, but not all have an ID (no value).
For testing, Im putting it into an XML variable, then using this to parse it out... this gives me the results I want, Table IDs in one col and the other IDs in another.

sql Code:

declare @GLXML xml = '<MAPPEDVALUES>
  <ROW>
    <ID>99999999-9999-9999-9999-999999999999</ID>
    <PDACCOUNTCODEMAPOFFICEID>1A692E94-3214-44B8-A658-F2927230BC25</PDACCOUNTCODEMAPOFFICEID>
    <REVENUETYPEID>E94C5D99-45C3-4832-9394-E52D4639438C</REVENUETYPEID>
    <APPLICATIONTYPEID>ECA6E4A7-8B0B-4E80-BAFF-07A3559E4AD4</APPLICATIONTYPEID>
    <PAYMENTMETHODID>C24896E3-F243-45A1-BF2C-DA333C2B7F44</PAYMENTMETHODID>
    <PDACCOUNTSYSTEMID>4B121C2C-CCE6-440D-894C-EA0DEF80D50B</PDACCOUNTSYSTEMID>
    <SEGMENT02TABLEID>1BA6F27F-8454-4FC4-A830-3FDA8CD35D04</SEGMENT02TABLEID>
    <SEGMENT03ID>68F1B5E8-9BD5-4D9B-B2F0-12F1A7E347B6</SEGMENT03ID>
    <SEGMENT03TABLEID>3298E2CE-EF37-4681-AC12-050979B6C5F3</SEGMENT03TABLEID>
    <SEGMENT04ID>6300D932-0E86-4C2C-BE09-6791F5E41E55</SEGMENT04ID>
    <SEGMENT04TABLEID>AEBA8FD5-42B6-4D37-93EB-8916BEC1385A</SEGMENT04TABLEID>
    <SEGMENT05TABLEID>3EF5D061-5692-40F3-B8AD-1367A8C6FAD5</SEGMENT05TABLEID>
    <SEGMENT06ID>A044FAD4-D902-4D56-B197-FBD8877C9AE2</SEGMENT06ID>
    <SEGMENT06TABLEID>5B93DEF5-AAA7-482E-B8F7-169EE0DAA53D</SEGMENT06TABLEID>
    <ERRORMESSAGE></ERRORMESSAGE>
    <ERRORCODE>0</ERRORCODE>
    <HASHVALUE>0</HASHVALUE>
    <MD5HASHVALUE>087C5BE7-2DFD-A5B4-5822-288EC0A2DDC1</MD5HASHVALUE>
  </ROW>
</MAPPEDVALUES>'
 
; with GLSEGMENTS as (
    SELECT 
        T.c.value('(SEGMENT02TABLEID)[1]', 'uniqueidentifier') AS SEGMENT02TABLEID,
        T.c.value('(SEGMENT02ID)[1]', 'uniqueidentifier') AS SEGMENT02ID,
        T.c.value('(SEGMENT03TABLEID)[1]', 'uniqueidentifier') AS SEGMENT03TABLEID,
        T.c.value('(SEGMENT03ID)[1]', 'uniqueidentifier') AS SEGMENT03ID,
        T.c.value('(SEGMENT04TABLEID)[1]', 'uniqueidentifier') AS SEGMENT04TABLEID,
        T.c.value('(SEGMENT04ID)[1]', 'uniqueidentifier') AS SEGMENT04ID,
        T.c.value('(SEGMENT05TABLEID)[1]', 'uniqueidentifier') AS SEGMENT05TABLEID,
        T.c.value('(SEGMENT05ID)[1]', 'uniqueidentifier') AS SEGMENT05ID,
        T.c.value('(SEGMENT06TABLEID)[1]', 'uniqueidentifier') AS SEGMENT06TABLEID,
        T.c.value('(SEGMENT06ID)[1]', 'uniqueidentifier') AS SEGMENT06ID
    FROM   @GLXML.nodes('/MAPPEDVALUES/ROW') T(c)
)
select SEGMENT02TABLEID as TABLEID, SEGMENT02ID as VALUEID from GLSEGMENTS
union all
select SEGMENT03TABLEID as TABLEID, SEGMENT03ID as VALUEID from GLSEGMENTS
union all
select SEGMENT04TABLEID as TABLEID, SEGMENT04ID as VALUEID from GLSEGMENTS
union all
select SEGMENT05TABLEID as TABLEID, SEGMENT05ID as VALUEID from GLSEGMENTS
union all
select SEGMENT06TABLEID as TABLEID, SEGMENT06ID as VALUEID from GLSEGMENTS

I'm using a CTE to shred the XML, then selecting each pair from the CTE and unioning it all together.
Luckily there's only the 5 segments (Segment 01 is a built-in hidden segment). so it keeps the unions small... but it just doesn't feel right or natural.

I think I can build a TF around this so I can pass in the XML (which actually comes from a function code elsewhere... this whole query is a mess, but it works, and I'm already 20 hrs over budget on it due to something else that "sounded simple at the time"... until I looked into the mechanics of it and how the system dealt with it... ungh... there are times when I wonder what in the world were they thinking when they designed this stuff... they certainly don't think about us in trenches that has to then contend with the results.

-tg

I'm going to go ahead and mark this thread as resolved... I've got something that will fit my needs. Turns out I was over thinking and over engineering it once again. I discovered that I don't need to unpivot or union any of the data, I just needed to shred the XML back into a single row and then deal with it accordingly. It just took me being up half the night staring at it to figure this out. Once I unioned the data as I did above, I realized I was losing the context... and then I ended up with multiple rows in my result, which isn't the desired effect. Sigh.

So the solution ended up being: shred the XML into a single row, then join it to another CTE multiple times for the different segments. Part of what threw me was that the individual segment numbers (02-06) weren't in the same order I was expecting. The ordering is tracked in a different area, and the part of the system that creates these segments doesn't care what order they end up in, it just knows it needs to create the segments, and it's someone (or something) else's job to sort them into the right order. Yay. :ehh:

So I'm marking this resolved. Sometimes all I need to do is just talk it out before the solution presents itself.

-tg

Code:

---

SELECT T1.Value AS TABLEID,T2.Value AS VALUEID FROM
(
        select  C.value('local-name(.)', 'varchar(50)') as NodeName,
                        C.value('(.)[1]', 'varchar(50)') as Value
        FROM @GLXML.nodes('/MAPPEDVALUES/ROW/*') AS T(C)
) T1
LEFT JOIN
(
        select  C.value('local-name(.)', 'varchar(50)') as NodeName,
                        C.value('(.)[1]', 'varchar(50)') as Value
        FROM @GLXML.nodes('/MAPPEDVALUES/ROW/*') AS T(C)
) T2
ON T2.NodeName LIKE 'SEGMENT__ID' AND T1.NodeName LIKE 'SEGMENT__TABLEID' AND LEFT(T1.NodeName,9)=LEFT(T2.NodeName,9)
WHERE T1.NodeName LIKE 'SEGMENT__TABLEID'

---