XML in SQL Server Management einlesen

[Anna85]

Guten Morgen,
Ich versuche krampfhaft eine XML-Datei auszulesen aber es gelingt mir nicht.

Die XML Datei im grob sieht so aus:
<collection xmlns="http://www.loc.gov/MARC21/slim"> <record type="Authority"> <leader>00000nz a2200000nc 4500</leader> <controlfield tag="001">1011387409</controlfield> <controlfield tag="003">DE-101</controlfield> <controlfield tag="005">20150227101359.0</controlfield> <controlfield tag="008">110430n</controlfield> <datafield tag="024" ind1="7" ind2=" "> <subfield code="a">http://d-nb.info/brd/123</subfield> <subfield code="2">uri</subfield> </datafield> <datafield tag="035" ind1=" " ind2=" "> <subfield code="a">(DE-101)105f563</subfield> </datafield> <datafield tag="035" ind1=" " ind2=" "> <subfield code="a">(DE-588)632a452</subfield> </datafield> </record> </collection>

Bis jetzt habe ich die Select Abfrage gemacht:

SELECT

orga.ref.value('leader[1]','varchar(255)') leader,
orga.ref.value('controlfield[1]','varchar(255)') controlfield,
orga.ref.value('datafield[1]','varchar(255)') datafield,
orga.ref.value('subfield[1]','varchar(255)') subfield


FROM (
--Abfrage auf Import-Tabelle
Select Top 1 xmlData
FROM dbo.orgaImportHistory
order by loadedDateTime desc
) xml

cross apply xml.xmlData.nodes('collection/record/datafield') orga(ref)

Allerdings als Ergebnis bekomme ich leere Zeilen, anstatt die Werte.
Ich behautpte, dass es um Namespace problem hier geht.
Aber seit zwei Tage finde ich leider keine Lösung und ich bin kein Programmierer.

Könnte mir jemnad weiter helfen?

Falls ich im falschen Forum bin, bitte mitteilen!
Viele Grüße aus Ulm!


P.S: Code von xml Datei, wenn ich hier kopiere, es rutsch alles zusammen. Die XML Code sieht natürich anders aus.

[defo2]

Ist das wirklich eine XML Datei?
Oder ist es eine XML Variable oder ein XML Feld in einem Datensatz einer normalen Tabelle?

[Anna85]

Hi, das ist eine xml Datei, aber hier kann ich das irgendwie nicht posten. ich versuche mit Code:

<collection xmlns="http://www.loc.gov/MARC21/slim">
   <record type="Authority">
       <leader>00000nz  a2200000nc 4500</leader>
       <controlfield tag="001">1011387409</controlfield>
       <controlfield tag="003">DE-101</controlfield>
       <controlfield tag="005">20150227101359.0</controlfield>
       <controlfield tag="008">110430n</controlfield>
       <datafield tag="024" ind1="7" ind2=" ">
         <subfield code="a">http://d-nb.info/brd/123</subfield>
         <subfield code="2">uri</subfield>
       </datafield>
       <datafield tag="035" ind1=" " ind2=" ">
         <subfield code="a">(DE-101)105f563</subfield>
       </datafield>
       <datafield tag="040" ind1=" " ind2=" ">
          <subfield code="a">(DE-588)632a452</subfield>
       </datafield>
    </record>
</collection>

[Christian Marquardt]

Anna85 wenn du nur kommentierst, bekommt das keiner mit. Schreibe eine Antwort

Des Weiteren: Wenn es eine Datei ist, wie kommt es zu diesem Konstrukt

FROM (
--Abfrage auf Import-Tabelle

Bezweifle, das MSSQL SQL Statements auf Dateien ausführt

[defo2]

Also wenn es aus einer Datei kommt (und die nirgends vorverarbeitet wird, siehe ~ "--Abfrage auf Import-Tabelle" irgendwo im Code), dann musst Du die XML Datei selbst öffnen.
Hier ist ein Beispiel wahllos aus dem Netz, ich nutze selbst kein MSSQL:

DECLARE @xmlFile XML

SET @xmlFile = (SELECT * FROM OPENROWSET(BULK ‘c:\myxml.xml’, SINGLE_CLOB) AS xmldata)

SELECT @xmlFile.value(‘(//*/process/@currentdb)[1]’, ‘varchar(10)’) AS DatabaseID

Zeile1: Variable für XML deklarieren
Zeile2: Datei öffnen und inhalt in XML Varianle schieben
Zeile3: Select statement auf XML Variable ausführen.

[Anna85]

Hallo
Christian Marquardt



Die Datei ist in SQL Server Management importiert, also wenn ich eine Abfrage mache:

select * from dbo.orgaImportHistory

bekomme ich als Ergebnis die xml Datei, die ich anklicken kann und sehe ich der Aufbau der xml (s. oben)

Mein Job ist die Felder, die ich geschrieben habe in eine Tabelle zu packen, damit das so aussieht:
leader | controlfield etc

Wenn ich select mache um die Daten zu holen:

SELECT

orga.ref.value('leader[1]','varchar(255)') leader,
orga.ref.value('controlfield[1]','varchar(255)') controlfield,
orga.ref.value('datafield[1]','varchar(255)') datafield,
orga.ref.value('subfield[1]','varchar(255)') subfield


FROM (
--Abfrage auf Import-Tabelle
Select Top 1 xmlData
FROM dbo.orgaImportHistory
order by loadedDateTime desc
) xml

cross apply xml.xmlData.nodes('collection/record/datafield') orga(ref)

erhalte ich leider als Ergebnis 0 Zeilen, also bekomme ich die Spalten, die ich abfrage, aber leer.

Ich behaupte, hier ist ein namenspace Problem, die ich nicht lösen kann.

[Martin Honnen]

Laut https://docs.microsoft.com/en-us/sql...ql-server-2017 ist das Argument zur "nodes"-Funktion ja XQuery, also sollte

 
 cross apply xml.xmlData.nodes('declare namespace df = "http://www.loc.gov/MARC21/slim"; df:collection/df:record/df:datafield')

[Anna85]

Hi, aber wenn ich das namespace auch in cross apply mache, bekomme ich Fehler, dass

Meldung 208, Ebene 16, Status 1, Zeile 1
Ungültiger Objektname 'xml.xmlData.nodes'.

warum? und ohne namenspace bekomme ich den Fehler nicht, aber auch kein Ergebnis.

[Martin Honnen]

Das verlinkte Dokument auf docs.microsoft.com hat Beispiele, als Alternative wird dort auch "WITH XMLNAMESPACES" erklärt. Versuche mal mit den Beispielen deinen Code anzupassen, wenn das nicht funktioniert und es weiter einen Fehler gibt, dann poste bitte ein minimales, aber vollständiges Beispiel, das den Fehler produziert.

[Christian Marquardt]

Die Datei ist in SQL Server Management importiert, also wenn ich eine Abfrage mache:
select * from dbo.orgaImportHistory

Also handelt es sich um ein oder mehrere Felder aus der DB
Verschoben nach MSSQL

[Anna85]

Also handelt es sich um ein oder mehrere Felder aus der DB
Verschoben nach MSSQL

ja, es geht um die Felder, wie leader, controlfield ...

[Anna85]

Laut https://docs.microsoft.com/en-us/sql...ql-server-2017 ist das Argument zur "nodes"-Funktion ja XQuery, also sollte

cross apply xml.xmlData.nodes('declare namespace df = "http://www.loc.gov/MARC21/slim"; df:collection/df:record/df:datafield')

ach, ich habe noch zum Schluß orga(ref) vergessen,
Es funktioniert, ich bekomme allerdings als Lösung 25 Zeilen mit NULL.

[Martin Honnen]

Es ist nicht klar, welche Werte aus dem XML als Zeilen und Spalten ausgegeben werden sollen. Da ja alle Elemente in dem Namensraum sind, ist es vermutlich einfacher, mit WITH XMLNAMESPACES zu arbeiten:

DECLARE @x xml   
SET @x='<collection xmlns="http://www.loc.gov/MARC21/slim">
   <record type="Authority">
       <leader>00000nz  a2200000nc 4500</leader>
       <controlfield tag="001">1011387409</controlfield>
       <controlfield tag="003">DE-101</controlfield>
       <controlfield tag="005">20150227101359.0</controlfield>
       <controlfield tag="008">110430n</controlfield>
       <datafield tag="024" ind1="7" ind2=" ">
         <subfield code="a">http://d-nb.info/brd/123</subfield>
         <subfield code="2">uri</subfield>
       </datafield>
       <datafield tag="035" ind1=" " ind2=" ">
         <subfield code="a">(DE-101)105f563</subfield>
       </datafield>
       <datafield tag="035" ind1=" " ind2=" ">
          <subfield code="a">(DE-588)632a452</subfield>
       </datafield>
    </record>
</collection>'; 

WITH XMLNAMESPACES ('http://www.loc.gov/MARC21/slim' AS df)
SELECT 
orga.ref.value('../df:leader[1]','varchar(255)') leader,
orga.ref.value('../df:controlfield[1]','varchar(255)') controlfield,
orga.ref.value('@tag','varchar(255)') datafield,
orga.ref.value('df:subfield[1]','varchar(255)') subfield

FROM  @x.nodes('df:collection/df:record/df:datafield') orga(ref)  
GO

ergibt dann

leader    controlfield    datafield    subfield
00000nz  a2200000nc 4500    1011387409    024    http://d-nb.info/brd/123
00000nz  a2200000nc 4500    1011387409    035    (DE-101)105f563
00000nz  a2200000nc 4500    1011387409    035    (DE-588)632a452

[Anna85]

Hallo,

alles klar ich habe das so gemacht:

 select orga.ref.value('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:leader[1]','varchar(255)') leader,
orga.ref.value('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:controlfield[1]','varchar(255)') controlfield,
orga.ref.value('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:datafield[1]/@tag','varchar(255)') datafield,
orga.ref.value('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:datafield[1]/marc21:subfield[1]','varchar(255)') subfield
from xmL xml outer apply xml.xmlData.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:collection/marc21:record') orga(ref)

aber natürlich namespacer vorher zu definieren, ist viel schlauer, danke!!!! Im Ergebnis kommt immer das 1. leader und 1. controlfield, datafield,und dann subfield, was er findet. Gibt es Möglichkeit, Bedingungen zu definieren: Ich möchte dass er folgendes rauskommt:

 controlfield wenn tag ="001", (datafield, wenn tag ="024" and subfield code ="a"), (datafield, wenn tag ="035" and subfield code ="a" )
Als Lösung wird dann kommen:
controlfield | datafield/subfield | datafield/subfield
1011387409 | http://d-nb.infi/gnd...|(DE-101)1011387409 

[defo2]

Wenn Du Felder im Select abrufen und ausgeben kannst, sollte das auch für die Filterung funktionieren oder?
Ich würde einfach mal ne Where Clause dran hängen und passende Kriterien eintragen.

[Anna85]

Ich bin dabei, bis jetzt kein Ergebnis, aber ich gebe Hoffnung nicht ab :-)
Ich melde mich, wenn ich verzweifle.