Get all Attributes from this XML

  • info 58414

    Hall of Fame

    Points: 3220

    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: 26684

    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: 3220

    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: 26684

    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: 3220

    as Table with separate Col.

  • drew.allen

    SSC Guru

    Points: 76458

    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: 442116

    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: 993882

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • drew.allen

    SSC Guru

    Points: 76458

    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