Extrahieren von data aus einer Tabelle mit XML-Spalte im SQL-server

Ich habe die unten Beispiel XML gespeichert in einer Tabelle namens CE_ExtendedElements und Spalte mit dem Namen xmlValue

<SIF_ExtendedElements xmlns=""> <SIF_ExtendedElement Name="EmergencyNumber">00000000</SIF_ExtendedElement> <SIF_ExtendedElement Name="EmergencyNumber">00000000</SIF_ExtendedElement> <SIF_ExtendedElement Name="Qualifications"> <Qualification> <Qualification>Bsc</Qualification> <QualificationWhenHired>True</QualificationWhenHired> <Major>Compuetr</Major> <GraduationYear>1993</GraduationYear> <Institution>Home Univ</Institution> <CountryID>1234</CountryID> </Qualification> <Qualification> <Qualification>Bsc</Qualification> <QualificationWhenHired>True</QualificationWhenHired> <Major>Compuetr</Major> <GraduationYear>1993</GraduationYear> <Institution>Home Univ</Institution> <CountryID>1234</CountryID> </Qualification> </SIF_ExtendedElement> <SIF_ExtendedElement Name="JoinDate">2003-09-15T00:00:00</SIF_ExtendedElement> </SIF_ExtendedElements> 

Die maximalen Knoten für jedes Element sind nur 2 (der Benutzer kann 2 EmergencyNumber und 2 Qualifikationen für jeden datasatz hinzufügen).

Kann jemand mir beim Lesen der oben genannten XML mit SQL-statement helfen, die XML-Knoten als Spalten zu konvertieren? Gleich wie die untenstehende Tabelle:

EmergencyNumber1 | EmergencyNumber2 | Qualifikation1 | QualifikationWhenHired1 | Major1 | ….. Qualifikation2 | QualifikationWhenHired2 | Major2 | …..

Vielen Dank

Ich muss sagen, dass die Struktur Ihres XML scheint nicht das Beste zu sein …

Aber du möchtest es so ausprobieren:

Achtung, verwendet ein Dummy-Namespaces und änderte die data für eine bessere Kontrolle

 DECLARE @xml XML= '<SIF_ExtendedElements xmlns="http://dummy_uri"> <SIF_ExtendedElement Name="EmergencyNumber">11111111</SIF_ExtendedElement> <SIF_ExtendedElement Name="EmergencyNumber">22222222</SIF_ExtendedElement> <SIF_ExtendedElement Name="Qualifications"> <Qualification> <Qualification>Bsc1</Qualification> <QualificationWhenHired>True</QualificationWhenHired> <Major>Compuetr</Major> <GraduationYear>1991</GraduationYear> <Institution>Home Univ</Institution> <CountryID>1111</CountryID> </Qualification> <Qualification> <Qualification>Bsc2</Qualification> <QualificationWhenHired>True</QualificationWhenHired> <Major>Compuetr</Major> <GraduationYear>1992</GraduationYear> <Institution>Home Univ</Institution> <CountryID>2222</CountryID> </Qualification> </SIF_ExtendedElement> <SIF_ExtendedElement Name="JoinDate">2003-09-15T00:00:00</SIF_ExtendedElement> </SIF_ExtendedElements>'; WITH XMLNAMESPACES(DEFAULT 'http://dummy_uri') SELECT Elmt.value('(SIF_ExtendedElement[@Name="EmergencyNumber"])[1]','varchar(max)') AS EmergencyNumber1 ,Elmt.value('(SIF_ExtendedElement[@Name="EmergencyNumber"])[2]','varchar(max)') AS EmergencyNumber2 ,Qlfct.value('Qualification[1]/Qualification[1]','varchar(max)') AS Qualification1 ,Qlfct.value('Qualification[1]/QualificationWhenHired[1]','bit') AS QualificationWhenHired1 ,Qlfct.value('Qualification[1]/Major[1]','varchar(max)') AS Major1 ,Qlfct.value('Qualification[1]/GraduationYear[1]','int') AS GraduationYear1 ,Qlfct.value('Qualification[1]/Institution[1]','varchar(max)') AS Institution1 ,Qlfct.value('Qualification[1]/CountryID[1]','int') AS CountryID1 ,Qlfct.value('Qualification[2]/Qualification[1]','varchar(max)') AS Qualification2 ,Qlfct.value('Qualification[2]/QualificationWhenHired[1]','bit') AS QualificationWhenHired2 ,Qlfct.value('Qualification[2]/Major[1]','varchar(max)') AS Major2 ,Qlfct.value('Qualification[2]/GraduationYear[1]','int') AS GraduationYear2 ,Qlfct.value('Qualification[2]/Institution[1]','varchar(max)') AS Institution2 ,Qlfct.value('Qualification[2]/CountryID[1]','int') AS CountryID2 FROM @xml.nodes('/SIF_ExtendedElements') AS A(Elmt) CROSS APPLY Elmt.nodes('SIF_ExtendedElement[@Name="Qualifications"]') AS B(Qlfct) 
 DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) SELECT @XML = XMLvalue FROM CE_ExtendedElements EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML SELECT EmergencyNumber1, EmergencyNumber2, Qualification, QualificationWhenHired, Major, Institution, CountryID FROM OPENXML(@hDoc, 'SIF_ExtendedElements/Qualifications/Qualification') WITH ( EmergencyNumber1 [varchar](50) '../../@EmergencyNumber1', EmergencyNumber2 [varchar](50) '../../@EmergencyNumber2', Qualification [varchar](100) '@Qualification', QualificationWhenHired [varchar](50) '@QualificationWhenHired', Major [varchar](50) '@Major', Institution [varchar](50) '@Institution', CountryID [varchar](50) '@CountryID' ) EXEC sp_xml_removedocument @hDoc GO 

Das sollte ziemlich nah sein!