Parse XML in SQL Server

Question

The XML looks like this:

<GespeicherteDaten>
    <strategieWuerfelFelder Type="strategieWuerfelFelder">
        <Felder X="3" Y="3" Z="3">
            <Feld X="1" Y="1" Z="1">
                <strategieWuerfelFeld Type="strategieWuerfelFeld">
                    <Name>Name</Name>
                    <Beschreibung>Test</Beschreibung>
                </strategieWuerfelFeld>
            </Feld>
            <Feld X="1" Y="1" Z="2">
                <strategieWuerfelFeld Type="strategieWuerfelFeld">
                    <Name>Name2</Name>
                    <Beschreibung>Test2</Beschreibung>
                </strategieWuerfelFeld>
            </Feld>
        </Felder>
    </strategieWuerfelFelder>
</GespeicherteDaten>'

My result table should contain the attributes Felder->X, Y, Z, Feld->X,Y,Z and Name.

Like:

Felder_X | Felder_Y | Felder_Z | Feld_X | Feld_Y | Feld_Z | Name

Is it possible to get this values directly from a query or stored procedure?

bummi · Accepted Answer · 2013-03-28 12:14:08Z

38

DECLARE @xml xml
SET @xml = 
'<GespeicherteDaten>
<strategieWuerfelFelder Type="strategieWuerfelFelder">
    <Felder X="3" Y="3" Z="3">
        <Feld X="1" Y="1" Z="1">
            <strategieWuerfelFeld Type="strategieWuerfelFeld">
                <Name>Name</Name>
                <Beschreibung>Test</Beschreibung>
            </strategieWuerfelFeld>
        </Feld>
        <Feld X="1" Y="1" Z="2">
            <strategieWuerfelFeld Type="strategieWuerfelFeld">
                <Name>Name2</Name>
                <Beschreibung>Test2</Beschreibung>
            </strategieWuerfelFeld>
        </Feld>
    </Felder>
</strategieWuerfelFelder></GespeicherteDaten>'

SELECT 
    b.value('@X', 'int') as X
  , b.value('@Y', 'int') as Y
  , b.value('@Z', 'int') as Z
  , b.value('(./strategieWuerfelFeld/Name/text())[1]','Varchar(50)') as [Name]
  , b.value('../@X','int') as Felder_X
  , b.value('../@Y','int') as Felder_Y
  , b.value('../@Z','int') as Felder_Z  
FROM @xml.nodes('/GespeicherteDaten/strategieWuerfelFelder/Felder/Feld') as a(b)

edited Mar 28, 2013 at 12:14

answered Mar 28, 2013 at 12:07

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Smith Over a year ago

excellent, short and to the point. i was able to utilize this without much thought which is always nice :-)

Thomas Over a year ago

What is the a(b) syntax doing?

Mikael Eriksson · Accepted Answer · 2013-03-28 12:06:10Z

declare @XML xml 
set @XML = '
<GespeicherteDaten>
    <strategieWuerfelFelder Type="strategieWuerfelFelder">
        <Felder X="3" Y="3" Z="3">
            <Feld X="1" Y="1" Z="1">
                <strategieWuerfelFeld Type="strategieWuerfelFeld">
                    <Name>Name</Name>
                    <Beschreibung>Test</Beschreibung>
                </strategieWuerfelFeld>
            </Feld>
            <Feld X="1" Y="1" Z="2">
                <strategieWuerfelFeld Type="strategieWuerfelFeld">
                    <Name>Name2</Name>
                    <Beschreibung>Test2</Beschreibung>
                </strategieWuerfelFeld>
            </Feld>
        </Felder>
    </strategieWuerfelFelder>
</GespeicherteDaten>'


select Felder.N.value('@X', 'int') as Felder_X,
       Felder.N.value('@Y', 'int') as Felder_Y,
       Felder.N.value('@Z', 'int') as Felder_Z,
       Feld.N.value('@X', 'int') as Feld_X,
       Feld.N.value('@Y', 'int') as Feld_Y,
       Feld.N.value('@Z', 'int') as Feld_Z,
       Feld.N.value('(strategieWuerfelFeld/Name/text())[1]', 'nvarchar(100)') as Name
from @XML.nodes('/GespeicherteDaten/strategieWuerfelFelder/Felder') as Felder(N)
  cross apply Felder.N.nodes('Feld') as Feld(N)

Result:

Felder_X    Felder_Y    Felder_Z    Feld_X      Feld_Y      Feld_Z      Name
----------- ----------- ----------- ----------- ----------- ----------- ---------
3           3           3           1           1           1           Name
3           3           3           1           1           2           Name2

Collectives™ on Stack Overflow

Parse XML in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related