How to use XPath to read identical elements with different Text Values

[yingwen]

Hi,

I have a XML, which I don't have any choice to modify the structure of it.


<MemberAddress>
<xAL:FreeTextAddress>
<xAL:AddressLine>123 Street Name</xAL:AddressLine>
<xAL:AddressLine>Suburb, City</xAL:AddressLine>
</xAL:FreeTextAddress>
<xAL:Country>
<xAL:Name>New Zealand</xAL:Name>
</xAL:Country>
<xAL:PostCode>
<xAL:Identifier>6011</xAL:Identifier>
</xAL:PostCode>
</MemberAddress>

I would like to write a stored procedure with XPath to read 2 addressLines in SQL Server.

AddressLine1 varchar(30) 'n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine',
AddressLine2 varchar(30) 'n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine',

The code above only give me "123 Street Name" for both fields. Just like to know how can I get xPath to read "Suburb, City" for the second field?

Thanks

[yingwen]

I have found out the answer by myself. Thanks.

Just use

AddressLine1 varchar(30) 'child::n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine[position() = 1]',
AddressLine2 varchar(30) 'child::n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine[position() = 2]',

[dorinbogdan]

Ok, glad to hear that.

Thank you for replying the solution.

God bless you,
Dorin.