Get all Attributes from this XML

Question

Get all Attributes from this XML

info 58414

Hall of Fame

Points: 3386
More actions
December 12, 2018 at 6:17 am

#409379

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 🙂

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

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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: 3386 More actions · Answer 2

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: 26944 More actions · Answer 3

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: 3386 More actions · Answer 4

info 58414

Hall of Fame

Points: 3386

December 12, 2018 at 9:19 am

#2016109

as Table with separate Col.

drew.allen SSC Guru Points: 76944 More actions · Answer 5

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: 442467 More actions · Answer 6

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: 1003863 More actions · Answer 7

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.

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

drew.allen SSC Guru Points: 76944 More actions · Answer 8

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