CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008-r2
DeadCat
DeadCat

Reputation: 192

Select and match values from two XML columns

I have two XML. One for Old Value and another for New Value. Both XMl are the columns of a table.

Table A

| ID  | Old Value                        | New Value                        |
+-----+----------------------------------+----------------------------------+
| 1   | <Root>                           | <Root>                           |
|     | <ScreenrightsManagement>         | <ScreenrightsManagement>         |
|     | <SCREENRIGHTID>8</SCREENRIGHTID> | <SCREENRIGHTID>8</SCREENRIGHTID> |
|     | <SCREENID>9</SCREENID>           | <SCREENID>10</SCREENID>          |
|     | <ROLEID>1</ROLEID>               | <ROLEID>2</ROLEID>               |
|     | </ScreenrightsManagement>        | </ScreenrightsManagement>        |
|     | </Root>                          | </Root>                          |

Expected Output:

  Table Name                Field Name       Old Value    New Value
  ScreenrightsManagement    SCREENRIGHTID    8            8
  ScreenrightsManagement    SCREENID         9            10
  ScreenrightsManagement    ROLEID           1            2

I need to select the value from the XML and get the expected output as shown. How can i do it?

Upvotes: 2

Views: 187

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272336

The following will extract all name and values from XML and JOIN them together:

SELECT
    t.ID,
    o.nodes.value('local-name(.)', 'VARCHAR(100)') AS nodeName,
    o.nodes.value('.', 'VARCHAR(100)') value1,
    n.nodes.value('.', 'VARCHAR(100)') value2
FROM @tablea AS t
CROSS APPLY t.OldValue.nodes('/Root/ScreenrightsManagement/*') AS o(nodes)
CROSS APPLY t.NewValue.nodes('/Root/ScreenrightsManagement/*') AS n(nodes)
WHERE o.nodes.value('local-name(.)', 'VARCHAR(100)') = n.nodes.value('local-name(.)', 'VARCHAR(100)')

Demo on DB Fiddle

Upvotes: 2

NP007
NP007

Reputation: 688

Below query will help you to get result.

SELECT 
oldvalue.ID
,oldvalue.tablename
,oldvalue.Colname as fieldname
,oldvalue.ColValue as oldvalue
,newvalue.ColValue as newvalue

 FROM
(
SELECT 
    q.ID,
    t.p.value('local-name(.)', 'varchar(50)') as Colname,
    t.p.value('(.)[1]', 'varchar(50)') as ColValue,
    t.p.value('local-name(..)[1]', 'varchar(50)') as tablename
FROM #test q
    CROSS APPLY oldvalue.nodes('/Root/ScreenrightsManagement/*') t(p)
)oldvalue
inner join 
(
SELECT 
    q.ID,
    t.p.value('local-name(.)', 'varchar(50)') as Colname,
    t.p.value('(.)[1]', 'varchar(50)') as ColValue,
    t.p.value('local-name(..)[1]', 'varchar(50)') as tablename
FROM #test q
    CROSS APPLY newvalue.nodes('/Root/ScreenrightsManagement/*') t(p)
)newvalue ON newvalue.Id=oldvalue.Id and newvalue.Colname=oldvalue.Colname
 and newvalue.tablename=oldvalue.tablename

Upvotes: 4

Related Questions