I have a MS SQL Table containing user data, e.g. Username and ID. This list should be compared with another list which is available as XML Document. Fortunately MS SQL 2005 is able to process XML so I searched around and found a post from Divya Agrawal on MSSQLTips.com. In Divyas tip 2 XML files are compared, so I modified it to compare XML and SQL row.
Read the rest of this entry
I have a MS SQL Table containing user data, e.g. Username and ID. This list should be compared with another list which is available as XML Document. Fortunately MS SQL 2005 is able to process XML so I searched around and found a post from Divya Agrawal on MSSQLTips.com. In Divyas tip 2 XML files are compared, so I modified it to compare XML and SQL row. For this example, my SQL Table ‘Users’ has just 2 rows: “Name” (varchar(100)) and “ID” (int). My XML File looks like this: 1: <root>
2: <data>
3: <name>Thorsten Test</name>
4: <id>123</id>
5: </data>
6: <data>
7: <name>Peter Pommes</name>
8: <id>124</id>
9: </data>
10: <data>
11: <name>Wiki Pedia</name>
12: <id>125</id>
13: </data>
14: </root>
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
So I need to check whether ‘root/data/id’ from XML file is already in table column ‘ID’. If not, the name and the ID should be returned. This could be done with this little query:
1: CREATE PROCEDURE [dbo].[FindNewUsers]
2: @Userlist XML
3: AS
4: BEGIN
5: SET NOCOUNT ON;
6:
7: Select N.value('name[1]', 'varchar(100)') as Username,
8: N.value('id[1]', 'int') as ID
9: from @Userlist.nodes('/root/data') as X(N)
10: where N.value('id[1]', 'int') Not in (select ID from Users)
11: END
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
That’s it!
MS SQL 2005, xml