Get all Attributes from this XML

  • info 58414

    Hall of Fame

    Points: 3383

    Hi, how can i get a Resultlist (see below) from this XML?
    ------------------------------------------------------
    DECLARE @x XML;
    SET @x
      = N'
    <SessionStart xsi:noNamespaceSchemaLocation="http://www.itx.cc/psstart.xsd" sessionId="200" dateTime="2009-11-25T10:04:13.160"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Product itemClass="yard" itemType="SOA"/>
      <Operator employeeId="tony"/>
      <Entity stationId="700" stage="MUC" subStage="OST" line="" kitchen="" site="02"/>
      <Recipe recipeId="Salat" revision="1.0">
       <RecipeModule moduleId="AAAA" revision="AAAA.00.0001" type="CONFIGURATION"/>
       <RecipeModule moduleId="XXXX" revision="XXXX.05.0001" type="CONFIGURATION"/>
       <RecipeModule moduleId="NNNN" revision="NNNN.00.0001" type="CONFIGURATION"/>
       <RecipeModule moduleId="RRRR" revision="RRRR.00.0001" type="CONFIGURATION"/>
       <RecipeModule moduleId="SSSS" revision="SSSS.00.0001" type="CONFIGURATION"/>
       <RecipeModule moduleId="BBBB" revision="BBBB.00.0001" type="CONFIGURATION"/>
      </Recipe>
    </SessionStart>';
    ------------------------------------------------------

    Like this  Xpath-Expression and Result: /SessionStart/*/attribute::*
    or  Flat-Format.

    Attribute itemClass : yard
    Attribute itemType : SOA
    Attribute employeeId : tony
    Attribute stationId : 700
    Attribute stage : MUC
    Attribute subStage : OST
    Attribute line :
    Attribute kitchen :
    Attribute site : 02
    Attribute recipeId : Salat
    Attribute revision : 1.0
    Attribute moduleId: AAAA
    Attribute revision: AAAA.00.0001
    Attribute type: CONFIGURATION
    .... and so on...

    Thanks and Regards  Nicole 🙂

  • Mark Cowne

    One Orange Chip

    Points: 26752

    SELECT  'Attribute ' +  t.c.value('local-name(.)', 'varchar(50)') + ' : ' +
                            t.c.value('.', 'varchar(100)') AS Result
    FROM   @x.nodes('/SessionStart/*//attribute::*') AS t(c);

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • info 58414

    Hall of Fame

    Points: 3383

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

  • Mark Cowne

    One Orange Chip

    Points: 26752

    info 58414 - Wednesday, December 12, 2018 8:24 AM

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

    Not sure what you mean by 'flat display (horizontal)'

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • info 58414

    Hall of Fame

    Points: 3383

    as Table with separate Col.

  • drew.allen

    SSC Guru

    Points: 76735

    info 58414 - Wednesday, December 12, 2018 8:24 AM

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

    I have a tip: Google is your friend.  There is nothing particularly tricky about your XML and you should be able to figure this out on your own with a little investigation.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lynn Pettis

    SSC Guru

    Points: 442333

    Mark Cowne - Wednesday, December 12, 2018 8:29 AM

    info 58414 - Wednesday, December 12, 2018 8:24 AM

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

    Not sure what you mean by 'flat display (horizontal)'

    Think pivot.

  • Jeff Moden

    SSC Guru

    Points: 996642

    drew.allen - Wednesday, December 12, 2018 9:33 AM

    info 58414 - Wednesday, December 12, 2018 8:24 AM

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

    I have a tip: Google is your friend.  There is nothing particularly tricky about your XML and you should be able to figure this out on your own with a little investigation.

    Drew

    I'm thinking that the request is meant for the dynamic construction of the flattened result set.  In other words, the entity names are not known ahead of time.  Knowling little about XQuery and the like, I wouldn't have a clue what to Google for.  Have a link or an example?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • drew.allen

    SSC Guru

    Points: 76735

    Jeff Moden - Wednesday, December 12, 2018 9:00 PM

    drew.allen - Wednesday, December 12, 2018 9:33 AM

    info 58414 - Wednesday, December 12, 2018 8:24 AM

    Great Mark! Only view Lines for this big Result 😉
    do you still have a tip for me, how do I bring the result in a flat display (horizontal)?
    Regards Nicole

    I have a tip: Google is your friend.  There is nothing particularly tricky about your XML and you should be able to figure this out on your own with a little investigation.

    Drew

    I'm thinking that the request is meant for the dynamic construction of the flattened result set.  In other words, the entity names are not known ahead of time.  Knowling little about XQuery and the like, I wouldn't have a clue what to Google for.  Have a link or an example?

    I tend to assume that it's not dynamic unless there is something that indicates otherwise.  Even so, programming something dynamic requires a solid foundation in the basics, and I haven't seen any indication that this is the case.  I would just Google "XQuery examples SQL Server".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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