XML get result

  • 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 1 year, 3 months ago by  murstfirst.
    • This topic was modified 1 year, 3 months ago by  murstfirst.
    • This topic was modified 1 year, 3 months ago by  murstfirst.
  • It is not possible, as the value "Avis" does not exist in the sample data

  • 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"?>

    <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)')

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

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks you so much!

  • 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)' );

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

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