Passing Multiple-value With XML In SQL Server 2005

The XML functions has been improved a lot in SQL Server 2005. Suppose there's a simple Contacts table:

Contacts(Name varchar(100), Phone varchar(100), Email varchar(100))

The application is sending following XML data to SQL Server for Contacts update (simply string type for ADO.NET parameter):

<Contacts>
   <Contact>
      <Name>Name ABC</Name>
      <Phone>111-222-3333</Phone>
      <Email>abc@abc.com</Email>
    </Contact>
    <Contact>
      <Name>Name BCD</Name>
      <Phone>222-333-4444</Phone>
      <Email>bcd@bcd.com</Email>
    </Contact>
</Contacts>

The stored procedure takes the XML as parameter and parse it internally:

CREATE PROCEDURE [dbo].[Update_ContactsByXml]
(
    @Contacts xml
)
AS
BEGIN
    SET NOCOUNT ON 
    -- Create a temp table to hold the values
    declare @tmpItems table
    (
        Name varchar(100),
        Phone varchar(100),
        Email varchar(100)
    )
    -- Insert all Ids to temp table
    INSERT INTO @tmpItems
    SELECT  TMPXML.Nodes.value('./Name[1]',  'varchar(100)') as Name,
            TMPXML.Nodes.value('./Phone[1]', 'varchar(100)') as Phone,
            TMPXML.Nodes.value('./Email[1]', 'varchar(100)') as Email
    FROM  @Contacts.nodes('//Contacts/Contact') TMPXML (Nodes)
        
    -- Update existing contacts
    UPDATE C SET Phone = T.Phone, Email = T.Email
    FROM @tmpItems T INNER JOIN Contacts C ON T.Name = C.Name

    -- Insert new contacts
    INSERT INTO Contacts 
    SELECT * FROM @tmpItems WHERE Name NOT IN (SELECT Name FROM Contacts)
END

One advantage of using XML to pass multi-value is that it's not vulnerable to SQL injection attack.

A Few Releases

After long time of Beta the final release date of Java 6 (1.6) is confirmed to be released next month, which is a big news for Java community. In the Linux world, Fedora Core 6 was released last month, and its previous version Fedora Core 5 was released less than 7-month ago. Fedora looks like trying to catch up the installation/UI improvement brought by Ubuntu, who announced latest 6.10 release two weeks ago.

I used to work with C/Java in Unix/Linux, but most of my work in recent years is with Microsoft solutions, .NET and C# specifically. Now I pay more attention on MS technologies. So what's new in Microsoft world? Well Microsoft has long product lines, and new releases or updates are just too frequent: Team Foundation Server (TFS) 2006, BizTalk 2006, Office 2007, Windows Server 2008, etc...

What else? Microsoft has just released .NET 3.0 and announced the completion of SharePoint 2007 RTM today!

What's new in .NET 3.0? Mainly four components are added and Microsoft named them foundations:

• Windows Presentation Foundation (WPF): a new user interface subsystem and API based on XML and vector graphics, which uses 3D computer graphics hardware and Direct3D technologies.
• Windows Communication Foundation (WCF): a service-oriented messaging system which allows programs to interoperate locally or remotely similar to web services.
• Windows Workflow Foundation (WF): a workflow engine for task automation and integrated transactions.
• Windows CardSpace: a component securely storing a person's digital identities and provides a unified interface for choosing the identity for a particular transaction, such as logging in to a website.

What's the SharePoint 2007? SharePoint 2007 includes a free version of Windows SharePoint Services (WSS) 3.0, and a not free (actually quite expensive) product called Microsoft Office SharePoint Server (MOSS) 2007. MOSS 2007 is built on top of WSS 3.0 and offers many features that are not included in WSS 3.0.

I happen to have a chance to take some BizTalk 2006/SharePoint 2007 courses during the past 6 months. The course materials were based on the SharePoint 2007 Beta. I am very impressive of how SharePoint technologies can help a business to streamline line their process. Common IT tasks in company, such as creating/editing pages, adding/configuring web parts, configuring people permissions, document management, team collaborations, etc., now can be easily done by business people without IT guys' assistance.

Well you would say, what about developers and IT specialists? In this radically changing world, we need to learn more and more, right? True. New releases and new products indicate changes and new stuff. Change can be scary and you have to face it. But no matter C, Java or C#, Windows or Linux, your existing skills can transition in new technologies in some forms. Those fancy technologies and buzz words can be easily adopted since you have the fundamental of understanding how the computer world works. People living in this world all face too much information, especially in IT world. It would be great if we could filter all information around us efficiently.

Is there a course of "IT 101 - How To Avoid Being Overwhelmed" in school?