XML data load to sqlserver.

Question

XML data load to sqlserver.

demin99

SSC Eights!

Points: 927
More actions
April 23, 2019 at 6:04 am

#3521955

I have an XML which Iam trying load with the following SQL code. But Iam getting NULL values when I use sequence in Addressline column..
what am I missing..? Please help.
<Location>
<Address>
<AttentionOfName />
<AddressLine sequence="1">4455 sstreet </AddressLine>
<CityName>Any Town</CityName>
<PostalCode>77373</PostalCode>
</Address>
</Location>
Select column3.value('AddressLine[1]/@sequence[1]','nvarchar(50)') = '1' ) Address1
from ( select convert(XML,CustomerBOD) as CustBOD
from [dbo].[Customer] ) as Cust
outer apply CustBOD.nodes('/Location/Address/AddressLine') as TAB3(column3)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You haven't explained your goal here or the results you want, however, is this not what you are after?

DECLARE @XML xml = '
<Location>
    <Address>
        <AttentionOfName />
        <AddressLine sequence="1">4455 sstreet </AddressLine>
        <CityName>Any Town</CityName>
        <PostalCode>77373</PostalCode>
    </Address>
</Location>';

SELECT V.X.value('(Location/Address/AddressLine/text())[1]','varchar(800)') AS AddressLine
FROM (VALUES(@XML))V(X);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

The fact that AddressLine has a sequence attribute leads me to believe that multiple address lines are possible.

So I added a sequence="2" node to the XML, before sequence="1". So you can't just make the assumption that AddressLine[1] is the first line.

<AddressLine sequence="2">Suite 500</AddressLine>

<AddressLine sequence="1">4455 sstreet </AddressLine>

If you only want <AddressLine sequence="1"> nodes, you can put that in your nodes() path.

SELECT  Address1 = TAB3.column3.value('(./text())[1]', 'nvarchar(50)')
FROM @CustBOD.nodes('/Location/Address/AddressLine[@sequence="1"]') AS TAB3(column3)

If you want to pull out both address lines, use the sequence attribute in the value() path.

SELECT  Address1 = TAB3.column3.value('(AddressLine[@sequence="1"]/text())[1]', 'nvarchar(50)'),
        Address2 = TAB3.column3.value('(AddressLine[@sequence="2"]/text())[1]', 'nvarchar(50)')
FROM @CustBOD.nodes('/Location/Address') AS TAB3(column3)