CODEWITHSUNDEEP
sqlsql-serverxmltags
elle0087
elle0087

Reputation: 911

sql server read xml without inner tags

i m reading an xml, but i have a problem when i want to read it without its inner(child) tags. for example:

DECLARE @XMLToParse  XML

SET @XMLToParse =   '<Animals>
                       <LandAnimals>
                        <Animal>Baboon</Animal>
                        <Animal>Yak
                            <d>asd</d>
                        </Animal>
                        <Animal>Zebra</Animal>
                       </LandAnimals>
                    </Animals>'

SELECT  xmlData.A.value('.', 'VARCHAR(100)') AS Animal
FROM    @XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

this query extracts on the second record 'Yak asd', but i need only 'Yak'. how can i fix it?

thank you.!

Upvotes: 2

Views: 140

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

Change your call on .value() to get the text():

SELECT  xmlData.A.value('(./text())[1]', 'VARCHAR(100)') AS Animal
FROM    @XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

Upvotes: 2

Serg
Serg

Reputation: 22811

Specify text()

SELECT  xmlData.A.value('./text()[1]', 'VARCHAR(100)') AS Animal
FROM    @XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

Upvotes: 2

Related Questions