Looping through XML is SQL Server 2005 1

[blondebier]

Hi Guys,

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

 

[gmmastros]

Pop this in to a query window. It's not EVERYTHING you need, but hopefully it will help.

Declare @ApplicantDetailsXML VarChar(8000)

Set @ApplicantDetailsXML = '
<?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>
'

Declare @dochandle integer
EXEC sp_xml_preparedocument @docHandle OUTPUT, @ApplicantDetailsXML 

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

EXEC sp_xml_removedocument @docHandle

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I also want to mention that this works with SQL 2000. In 2005, there MAY be a better way that I am not familiar with.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blondebier]

Thanks for your help George.

Where you have the select *, I have assigned the individual bits of data to parameters like this;

SELECT @Flat = Flat, @HouseName = HouseName, @HouseNumber = HouseNumber, @Street = Street, @District = District, @Town = Town, @County = County, @Postcode = Postcode, @WSCountryCode = CountryCode, @AtAddressFrom = AtAddressFrom, @AtAddressTo = AtAddressTo, @WSAddressType = AddressType

Then after the With (...)

I have an insert into my addresses table.

There appears to be a problem though, it will only insert the first address. The second doesn't work...

Any ideas why that could be?

It could be that there are more than 5 addresses... so I need it to do this loop for all the addresses supplied

 

[gmmastros]

My advise is to NOT bother with the local variables. It'll just slow you down. Insert it 'at the same time' you are selecting it. For example...

[b]Insert Into Address(Flat, HouseName, HouseNumber, etc..)
SELECT Flat, HouseName, HouseNumber, etc...[/b]
FROM OPENXML(@docHandle, '//Applicant/Addresses/Address', 2)
With (
        Flat VarChar(20),
        HouseName VarChar(20),
        HouseNumber VarChar(20),
        Street VarChar(20),
        District VarChar(20),
        Town VarChar(20),
        County VarChar(20),
        Postcode VarChar(20),
        CountryCode VarChar(20),
        AtAddressFrom VarChar(20),
        AtAddressTo VarChar(20),
        AddressType VarChar(20)
    )

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blondebier]

Sounds like a good idea. To add further complexity though I need to use the identity from each address insert for a further table. i.e. Customer_Addresses.

The @CustomerID is set further up so could I "Insert Into Customer_Addresses(@CustomerID, @@IDENTITY)" straight after the address insert?

 

[gmmastros]

Will the CustomerId be the same for all records? If so, then you can just add it along with the selects.

Insert Into Address([!]CustomerId[/!], Flat, HouseName, HouseNumber, etc..)
SELECT [!]@CustomerId[/!], Flat, HouseName, HouseNumber, etc...
FROM OPENXML(@docHandle, '//Applicant/Addresses/Address', 2)
With (etc...

And, you should STOP using @@Identity, it is potentially unreliable. Instead, you should use Scope_Identity().

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blondebier]

The CustomerID would be the same.

The only problem being that they are 2 tables.

Addresses and Customer_Addresses.

Any ideas? I'll look into Scope_Identity()

Thanks.

 

[gmmastros]

Let me guess. The addresses table has an identity field, and the Customer_Addresses table is just CustomerId and AddressId.

If this is true, I would ...

1. Create a temp table with a 'RowId' column added to it.
2. Select the data from the XML string in to this temp table.
3. Loop through the temp table.
4. Each time you insert a record in to the temp table, get the AddressId using Scope_Identity().
5. Update the temp table with this address id.
6. After you insert all the addresses, then insert in to the Customer_Addresses table from the temp table.

Make sure you wrap all this in a transaction.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blondebier]

Great. Thanks