sql server read xml without inner tags

Question

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.!

Gottfried Lesigang · Accepted Answer · 2016-12-06 11:18:43Z

2

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)

edited Dec 6, 2016 at 11:18

answered Dec 6, 2016 at 10:39

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

elle0087 Over a year ago

thank you very much! and so, are there any others options ilke 'text()'?

Gottfried Lesigang Over a year ago

@user3061212 What other options? You might use strange work-arounds but why?

Gottfried Lesigang Over a year ago

@user3061212 The /text() is the content which is the real textual content of an element. Reading the . with .value() will read the entire tree below the context node. You might read about .value('data(...)') if this is your intention. But in your case you are just not interested in lower elements...

Serg · Accepted Answer · 2016-12-06 10:42:02Z

2

Specify text()

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

answered Dec 6, 2016 at 10:42

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Collectives™ on Stack Overflow

sql server read xml without inner tags

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related