• Max Minkov (8/25/2009)


    Very insightful article!

    INSERT

    INTO [AddressBook]

    ([LastName], [FirstName],[Address])

    SELECT

    T.Item.query('./LastName').value('.', 'VARCHAR(25)') [LastName],

    T.Item.query('./FirstName').value('.', 'VARCHAR(25)') [FirstName],

    T.Item.query('./Address').value('.', 'AddressInfo') [Address],

    FROM @xmlAddressBook.nodes('/AddressBook/Contact') AS T(Item)

    You can use the following SQL syntax.

    SELECT

    T.Item.query('./LASTNAME').value('.', 'VARCHAR(25)') [LastName],

    T.Item.query('./FIRSTNAME').value('.', 'VARCHAR(25)') [FirstName],

    T.Item.query('./ADDRESS/STREET').value('.', 'Varchar(20)') + ','

    + T.Item.query('./ADDRESS/CITY').value('.', 'Varchar(20)') + ','

    + T.Item.query('./ADDRESS/STATE').value('.', 'Varchar(20)') [Address]

    FROM @xmlAddressBook.nodes('/ADDRESSBOOK/CONTACT') AS T(Item)

    Hope, you could have a Parse function in your CLR type defined, which takes a string type as an parameter and in the function you split the string. Hope you are using the delimiter as "," in the Parse function. (Plese check).

    The same delimiter , you have to add in SELECT + ',' , which you are using in the Parse method.

    hope this could help you to solve your problem.