Syntax issue SQL Server. Combining Pivot, XML parse and JOIN

Question

I have a column (varchar400) in the following form in an SQL table :

Info
User ID=1123456,Item ID=6685642

This column is used to store attributes of products in our database, and so while I am only concerned with User ID and Item ID, there may be superfluous information stored here, for example :

   Info
   Irrelevant ID=666,User ID=123124,AnotherIrrelevantID=1232342,Item ID=1213124

And so I have an SQL query as follows:

-- convert info column to xml type
; with cte as --imports a library of common table expressions
(
    select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,' ', ''),',', '" '),'=','="'),'.','') + '" />' as XML) info, --puts the OtherInformation column into well formed XML
    ROW_NUMBER() over (order by TableID) id --returns all rows??
    FROM Table
    WHERE TableEnum=51
) 
SELECT DISTINCT UserID from --selects unique user ids from our returned xml
(
       select T.N.value('local-name(.)', 'varchar(max)') as Name, --selects all attributes returned in varchar(max) format as Name
       T.N.value('.', 'varchar(max)') as Value, id --Selects all values returned
       from cte cross apply info.nodes('//@*') as T(N) -- from the XML we created above
) v
pivot (max(value) for Name in ([UserID])) p --creates a pivot table on Name, separating all of the attributes into different columns

Now, this correctly returns me a columns as follows :

UserID
1
2
3
4
5

Now I have another table, Table2, which holds the orders a user has made. I want to use the UserID as a reference into this table, and so instead of returning just UserID, I will return the rows on this table where the UserID I returned above equals the rows in this table.

So, instead of above, we get:

UserID    Table2Col   Table2Col2
2              Info        Info
5              Info        Info
5              Info2       Info2
5              Info3       Info3

2 questions - how can I perform a JOIN or do a subquery to combine the two tables, I cannot figure out how to do this with correct syntax. Secondly, I have written some comments on my query above that show how I understand the query to be working. Are they correct?

Answer 1

It is quite possible that I am missing something with your question, but it seems like you can just expand your existing query the following way. This still uses the CTE and the PIVOT, but the PIVOT query is placed in a subquery which allows you to join to table2:

; with cte as --imports a library of common table expressions
(
    select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,' ', ''),',', '" '),'=','="'),'.','') + '" />' as XML) info 
      , ROW_NUMBER() over (order by TableID)) id 
    FROM yourtable
) 
select d.userid, t2.col1, t2.col2
from
(
  SELECT DISTINCT UserID 
  from 
  (
    select T.N.value('local-name(.)', 'varchar(max)') as Name, 
      T.N.value('.', 'varchar(max)') as Value, id 
    from cte 
    cross apply info.nodes('//@*') as T(N) 
  ) v
  pivot 
  (
    max(value) 
    for Name in ([UserID])
  ) p 
) d
inner join table2 t2
  on d.userid = t2.userid;

See SQL Fiddle with Demo