Hi Guys,
The number of postings seem to be few, but if anyone is able to help I'd appreciate it.

Basically, I'm passing a string of XML as an nvarchar to a stored procedure. I then convert the string to XML using:

SET @XML = CONVERT(xml, RTrim(@ApplicantDetailsXML))

My Applicant Details XML may look like this:

<?xml version="1.0"?>
<Applicant>
    <Title>Mr</Title>
    <Forename>Tommy</Forename>
    <Surname>Test</Surname>
    <DateOfBirth>01/01/1975</DateOfBirth>
    <Gender>M</Gender>
    <Addresses>
        <Address>
            <Flat>
            </Flat>
            <HouseName>
            </HouseName>
            <HouseNumber>1</HouseNumber>
            <Street>Test Street</Street>
            <District>Test District</District>
            <Town>Test Town</Town>
            <County>Test County</County>
            <Postcode>TEST1</Postcode>
            <CountryCode>GB</CountryCode>
            <AtAddressFrom>01/01/2002</AtAddressFrom>
            <AtAddressTo>22/02/2006</AtAddressTo>
            <AddressType>U</AddressType>
        </Address>
        <Address>
            <Flat>Flat 2</Flat>
            <HouseName>Big House Of Flats</HouseName>
            <HouseNumber>2</HouseNumber>
            <Street>Test Street</Street>
            <District>Test District</District>
            <Town>Test Town</Town>
            <County>Test County</County>
            <Postcode>TEST2</Postcode>
            <CountryCode>GB</CountryCode>
            <AtAddressFrom>01/01/2000</AtAddressFrom>
            <AtAddressTo>31/12/2001</AtAddressTo>
            <AddressType>U</AddressType>
        </Address>
    </Addresses>
</Applicant>

I've setup a "loop"(it's not really) to go through the address nodes so that I can save them one at a time.

I can only seem to find an example like this:

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML
INSERT dbo.Address (HouseName)
    SELECT CONVERT(nvarchar(10), Text)
FROM OPENXML(@docHandle, '//Applicant/Addresses/Address/HouseName/text()', 1)
EXEC sp_xml_removedocument @docHandle

I can only get it to pick off one of the elements at a time. Are there any ways I can pick off all the elements for my insert at the same time?

I'd appreciate any tips on how best to handle this.
Thanks,
Francis

This does the job but I'm sure there must be a better way in 2005! If anyone can find out I'd be really interested to know.

DECLARE @dochandle integer
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML

SELECT @Flat = Flat, @HouseName = HouseName, @HouseNumber = HouseNumber,
        @Street = Street, @District = District, @Town = Town, @County = County,
        @Postcode = Postcode, @CountryCode = CountryCode, @AtAddressFrom = AtAddressFrom,
        @AtAddressTo = AtAddressTo, @AddressType = AddressType
FROM OPENXML(@docHandle, '//Applicant/Addresses/Address', 2)
With (

        Flat nchar(20),
        HouseName nchar(32),
        HouseNumber nchar(32),
        Street nchar(40),
        District nchar(40),
        Town nchar(40),
        County nchar(40),
        Postcode nchar(10),
        CountryCode smallint,
        AtAddressFrom datetime,
        AtAddressTo datetime,
        AddressType smallint
    )

INSERT INTO dbo.Addresses(
            AddressType,
            Flat,
            HouseName,
            HouseNumber,
            Street,
            District,
            Town,
            County,
            Postcode,
            CountryCode,
            AtAddressFrom,
            AtAddressTo
     )
     VALUES(
            1,
            @Flat,
            @HouseName,
            @HouseNumber,
            @Street,
            @District,
            @Town,
            @County,
            @Postcode,
            @CountryCode,
            @AtAddressFrom,
            @AtAddressTo
    )
EXEC sp_xml_removedocument @docHandle