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.