XML get result

Question

Post reply

XML get result

murstfirst

Old Hand

Points: 342
More actions
October 8, 2019 at 11:52 am

#3686929
Does anyone know how to get the value = "Avis" from this XML.
DECLARE @myDoc XML
DECLARE @ProdID VARCHAR(200)
SET @myDoc = '<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
<book category="web">
<title lang="en"> Kick Start</title>
<author>James McGovern</author>
aaaaaa
</book>
</bookstore>'
SELECT @myDoc.value('/bookstore/book/span/@class', 'varchar(200)')
- This topic was modified 4 years, 6 months ago by murstfirst.
- This topic was modified 4 years, 6 months ago by murstfirst.
- This topic was modified 4 years, 6 months ago by murstfirst.

Viewing 7 posts - 1 through 6 (of 6 total)

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

DesNorton SSC-Insane Points: 24277 More actions · Answer 1

It is not possible, as the value "Avis" does not exist in the sample data

How to post data/code on a forum to get the best help.

murstfirst Old Hand Points: 342 More actions · Answer 2

DesNorton wrote:

It is not possible, as the value "Avis" does not exist in the sample data

Strange I pasted it and it doesn't show in my post. I will pasting it again.

DECLARE @myDoc XML

DECLARE @ProdID VARCHAR(200)

SET @myDoc = '<?xml version="1.0" encoding="UTF-8"?>

<title lang="en"> Kick Start</title>

<author>James McGovern</author>

aaaaaa

</book>

</bookstore>'

SELECT @myDoc.value('/bookstore/book/span/@class', 'varchar(200)')

murstfirst Old Hand Points: 342 More actions · Answer 3

I have attached it. It looks like it doesn't show when i paste it. xml

Phil Parkin SSC Guru Points: 246693 More actions · Answer 4

DesNorton wrote:

It is not possible, as the value "Avis" does not exist in the sample data

I usually have a bad reaction when people use absolutes. Never say never 🙂

DECLARE @myDoc XML;

SET @myDoc
    = '<?xml version="1.0" encoding="UTF-8"?>

<bookstore>

<book category="web">

<title lang="en"> Kick Start</title>

<author>James McGovern</author>

aaaaaa
</book>

</bookstore>';

DECLARE @Str1 VARCHAR(500) = CAST(@myDoc AS VARCHAR(500));

SELECT SUBSTRING(@Str1, 19, 1) + SUBSTRING(@Str1, 87, 1) + SUBSTRING(@Str1, 35, 1) + SUBSTRING(@Str1, 6, 1);

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

murstfirst Old Hand Points: 342 More actions · Answer 5

murstfirst

Old Hand

Points: 342

October 8, 2019 at 1:05 pm

#3686967

Thanks you so much!

DesNorton SSC-Insane Points: 24277 More actions · Answer 6

Due to the clash of you div names with the web page, I am not recreating the actual xml here.

This should also get what you are looking for.

SELECT  @myDoc.value( '(bookstore/book/div)[1]/@data-store-name', 'varchar(200)' );

How to post data/code on a forum to get the best help.