Menu
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?
995
First, the sp_xml_preparedocument stored procedure parses the XML document. The parsed document is a tree representation of the nodes (elements, attributes, text, and comments) in the XML document. OPENXML then refers to this parsed XML document and provides a rowset view of all or parts of this XML document.
Support for XML is integrated into all the components in SQL Server in the following ways: The xml data type. XML values can be stored natively in an xml data type column that can be typed according to a collection of XML schemas, or left untyped. You can index the XML column.
Stored Procedure for reading, parsing and inserting XML data Once the nodes are fetched we need to extract the attribute and tag Inner Text values. For fetching the Inner Text values between the Tags we need to make use of the values function. The values function can read the Attribute as well as the Inner Text.
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) 
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
