Analizzare XML in SQL Server

L’XML si presenta così:

     Name Test     Name2 Test2     ' 

La mia tabella dei risultati dovrebbe contenere gli attributi Felder-> X, Y, Z, Feld-> X, Y, Z e Name.

Piace:

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

È ansible ottenere questi valori direttamente da una query o una stored procedure?

 DECLARE @xml xml SET @xml = '     Name Test     Name2 Test2    ' 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) 
 declare @XML xml set @XML = '      Name Test     Name2 Test2     ' 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) 

Risultato:

 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