XML in SQL Server Management einlesen

[Anna85]

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

Andere Frage, wenn ich laange xml habe, wie kann ich das insert machen, könnte man eine Unterabfrage oder so was einbauen?

[Anna85]

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.

tja, die Kriterien weiss ich es nicht, wie ich in dieser Sprache formulieren kann,, an sich ist einfach zu sagen:
wenn datafield tag='024", dann gib mir subfield code="a".

[defo2]

weiss ich es nicht, wie ich in dieser Sprache formulieren kann,, an sich ist einfach zu sagen:
wenn datafield tag='024", dann gib mir subfield code="a".

Was meinst du mit "in dieser Sprache"?
Falls Du XML meinst, das braucht man hier nicht mehr (außer Zugriffspfade/Nodesaufbau zu berücksichtigen.)
Falls Du SQL meinst, Du weißt aber, was eine "where clause" ist?

wenn ich laange xml habe, wie kann ich das insert machen

Auch hier ist (mir) das Problem unklar.
Willst Du nicht alles einfügen? Also gemäß Beispiel nicht 3 Datensätze sondern nur 1 Datensatz?

Wie würdest Du Deine Fähigkeiten einschätzen?
SQL= JA, XML = Nein

Du könntest Deine Frage konkreter formulieren, sonst weiß man nicht, was man Dir sagen soll.

Als allgemeiner Hinweis:
Wenn Du Deine XML Daten in SQL auslesen kannst (und das kannst Du ja nun), dann geht auch alles andere. Es ist dann eine reine SQL Problematik.
Als Vereinfachung kannst Du das funktionierende Statement in Klammern setzen und dann per alias ansprechen, als sei es eine Tabelle.

Also:

select X.leader, X.controlfield, X.datafiedl, X.subfield from
(..
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
  .. <plus andere felder>
   FROM @x.nodes ..
) X
  WHERE datafield ='024'

[Anna85]

"Auch hier ist (mir) das Problem unklar.
Willst Du nicht alles einfügen? Also gemäß Beispiel nicht 3 Datensätze sondern nur 1 Datensatz?"

Was ich hier gepostet habe ist ein Teil der XML Datei. Die besteht aus 100 Datensätze.
Ich möchte Sie ablesen mit Bedingungen, aber es geht nicht, was ich schreibe.

Meine SQL Schätzung ist 4 und XML eine gute 5.
Ich bin kein Programierer, ich versuche mir alleine alles hier einzulesen etc, was nicht einfach ist. Kannst du mir glauben, dazu noch eine Frau.

[Anna85]

select X.leader, X.controlfield, X.datafiedl, X.subfield from
(..
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
.. <plus andere felder>
FROM @x.nodes ..
) X
WHERE datafield ='024'

Leider mit der Bedingung geht funktioniert nicht.

Ich wollte so abfragen, mündlich:
Gib mir leader, controlfield tag ="001" (was das programm jetzt automatisch macht), datafield tag ="24" mit subfield code ="a",
datafield tag="040" mit subfield code ="a".

Hallo, wenn ich die Bedingung schreibe datafield ='024', dann sagte mir "Ungültiger Spaltenname 'datafield'."
wie kann die die Spaltenname verifizieren/anders definieren???

[Anna85]

Hallo, ich bin schon so weit gekommen, dass ich 1. gewünschte datafield bekomme, da es leicht ist,

aber ich möchte datafield = 035 noch auch ablesen, allerdings datafield =035 steht nicht immer auf 3. oder 4. Stelle, also ich kann nicht machen datafield[3].
Ich habe bei cross versucht dort eine Bedingung zu schreiben, aber es geht nicht.

Hat jemand eine Idee.

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[2]/@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 (

--Abfrage auf Import-Tabelle

Select Top 1 xmlData

FROM dbo.orgaImportHistory

--order by loadedDateTime desc

) xml

--cross apply xml.xmlData.nodes('collection/record') orga(ref)
--cross apply orga.ref.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:collection/marc21:record/datafield[@tag="110"]') orga(ref)
cross apply xml.xmlData.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:collection/marc21:record') as orga(ref)

Hätte jemand eine Idee, wo ich die Bedingung einbringen könnte?

[Anna85]

Ich habe es geschafft:

Hallo, ich habe es geschaft.
cross apply orga.ref.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:datafield[@tag="035"]') data035(ref)

Allerdings, habe ich z.B. datafield 035 zwei mal, und was ich schreibe liess mir das so ab, das ich zwei Zeilen habe:

Code: 1: 2: 3: leader controlfield datafield subfield 00000nz a2200000nc 4500 1011387409 035 (DE-101)105f563 00000nz a2200000nc 4500 1011387409 035 (DE-588)632a452

Was /Wie kann ich sagen, damit es nach komme ließ und damit es so aussieht:

Code: 1: 2: leader controlfield datafield subfield 00000nz a2200000nc 4500 1011387409 035 (DE-101)105f563, (DE-588)632a452

[defo2]

Tja, erstmal als Hinweis zum Forum, das ist leider so kaputt, dass man ältere Threads wie Deinen kaum verfolgen kann.

Zu Deinem Problem: Deine 2 Zeilen "entstehen" automatisch, da die varianten Subfelder gemeinsame "Parent" Felder haben. XML kann relationale Daten eindeutig darstellen, in einer Zeilen orientierten Darstellung aus SQL heraus müsstest du eine Aggregation durchführen.

Step 1: Du willst die XML Felder separat ansprechen können, am einfachsten so, wie sie im XML stehen, also
- '00000nz a2200000nc 4500' as leader,
- '1011387409 as controlfieldtag001,
dann die subs
- '(DE-101)105f563' as datafield35subA
usw.
also so ungefähr, wie Du es weiter oben über die Namespacedefinitionen gemacht hast. Alles was Du dort im Zugriff in Einzelfelder "zerlegst", kannst Du nachher in sql bequem und wie gewohnt per Feldname ansprechen.

Step 2:
Diese separaten Felder kannst Du dann wie gewünscht gezielt handhaben und sie im Select Teil direkt nennen oder aggregieren.
SQL = ~ select X.leader, X.controlfieldtag001, X.datafield35subA from (XML Aufbereitungs Statement) X
ergäbe im wesentlichen erstmal wieder das, was Du bereits erhälst in 2 Zeilen, aber separaten Spalten

Zu einer Zeile
Aggregat SQL = ~

SELECT X.leader, X.controlfieldtag001, STRING_AGG ( X.datafield35subA, ',')
  FROM (XML Aufbereitungs Statement) X
 GROUP BY X.leader, X.controlfieldtag001

Die 3 Spalten die Du nun erhälst, sollten nur eine Zeile bilden. Die Spalten kannst Du natürlich nach Bedarf zu einem Ausdruck konkatenieren.

[Anna85]

Hallo,

leider war ich erkrankt und ab heute wieder in der Arbeit.
Ich versuche mich einzufuchsen, aber leider ohne Erfolg.
Ich frage: könnte mir jemand eine Lösung geben, wie ich von dieser xml Datei: datafiled tag =24 mit code a, datafileld tag 035 mit code a auslesen kann? Gäbe es die Möglichkeit?

Viele Grüße

[defo2]

Ok, ich hoffe, Du bist auch wieder gesundet.
Meinerseits kann ich nicht einfach eine Lösung produzieren, ich arbeite nicht mal mit MSSQL, es wäre also für mich Kopfrechnen. Darin bin ich nicht mehr so gut.
Reinfuchsen muss sich nun sowieso auch das Publikum hier wieder. Und solche Foren sind auch keine Plattform für Fix&Fertig Lösungen, sowas gibt's auch, kostet aber Geld.

Vorschlag: Du schreibst zunächst mal Deinen letzten Stand hier rein, zum Reinfuchsen für alle:
- xml Daten
- latest and greatest select statement
- Ergebnisdaten

Und was am result noch nicht passt.

[Anna85]

Ok, ich hoffe, Du bist auch wieder gesundet.
Meinerseits kann ich nicht einfach eine Lösung produzieren, ich arbeite nicht mal mit MSSQL, es wäre also für mich Kopfrechnen. Darin bin ich nicht mehr so gut.
Reinfuchsen muss sich nun sowieso auch das Publikum hier wieder. Und solche Foren sind auch keine Plattform für Fix&Fertig Lösungen, sowas gibt's auch, kostet aber Geld.

Vorschlag: Du schreibst zunächst mal Deinen letzten Stand hier rein, zum Reinfuchsen für alle:
- xml Daten
- latest and greatest select statement
- Ergebnisdaten

Und was am result noch nicht passt.

Du hast Recht mit der Lösung, aber ich kann hier niemanden fragen und ich bin k Informatiker.
zu deinem Punkt:
1. die Struktur steht bei meinem ersten Post.
2. Ich mache das z.B so

 GO

;WITH XMLNAMESPACES(default 'http://www.loc.gov/MARC21/slim')

SELECT

--orga.ref.query('.')

orga.ref.value('leader[1]', 'nvarchar(125)') leader,

orga.ref.value('(.)[1]', 'nvarchar(125)') controlfield


FROM (

--Abfrage auf Import-Tabelle

Select Top 1 xmlData

FROM dbo.orgaImportHistory

--order by loadedDateTime desc

) xml

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

--cross apply orga.ref.nodes('controlfield') orga(ref)

und bekomme ich:

leader                                 |     controlfield
00000nz  a2200000nc 4500   |00000nz  a2200000nc 45001011387409DE-10120150227101359.0110430n||azznnaabn           | ana    |chttp://d-nb.info/gnd/7744007-

3. Ich muss folgenedes auslesen:
leader/controllfield tag ="001"/datafield tag ="024" mit code a/ datafield tag ="035" mit code a.

[Anna85]

Ich habe hier mehr erreicht, nämlich leader/controllfield und datafield , aber nur das erste controlfield mit tag 001, ich weiss es nicht, wie ich ablesen könnte controlvield 002.
Mit meinem code lese ich datafield 1 , datafield 2 , aber ich möchte anders sagen: gib mir datafield tag =001 oder datafield tag = 003, weil ich bei jedem Satz sind die datafields gleich. Es gibt leader, wo es gibt datafield tag 001, datafield tag 002, datafiled tag 035, aber gibt elader wo nur datafield 001 ist und dann ist falsch gelesen-

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[2]/@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 (

--Abfrage auf Import-Tabelle

Select Top 1 xmlData

FROM dbo.orgaImportHistory

--order by loadedDateTime desc

) xml

--cross apply xml.xmlData.nodes('collection/record') orga(ref)
--cross apply orga.ref.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:collection/marc21:record/datafield[@tag="110"]') orga(ref)
cross apply xml.xmlData.nodes('declare namespace marc21="http://www.loc.gov/MARC21/slim"; marc21:collection/marc21:record') as orga(ref)

[defo2]

Unsere Posts haben sich überschnitten, ich bezieh mich auf #26:
Das Ergebnis aus Beitrag #26 sah in #22schon besser aus, hier ist ja nun unter controlfield alles zusammengemixt.
Kannst Du nun bitte noch das Ergebnis zu #27 liefern?

[Anna85]

<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||azznnaabn           | ana    |c</controlfield>
    <datafield tag="024" ind1="7" ind2=" ">
      <subfield code="a">http://d-nb.info/gnd/7744007-9</subfield>
      <subfield code="2">uri</subfield>
    </datafield>
    <datafield tag="035" ind1=" " ind2=" ">
      <subfield code="a">(DE-101)1011387409</subfield>
    </datafield>
    <datafield tag="035" ind1=" " ind2=" ">
      <subfield code="a">(DE-588)7744007-9</subfield>
    </datafield>
    <datafield tag="035" ind1=" " ind2=" ">
      <subfield code="z">(DE-588c)7744007-9</subfield>
      <subfield code="9">v:zg</subfield>
    </datafield>
    <datafield tag="040" ind1=" " ind2=" ">
      <subfield code="a">DE-29</subfield>
      <subfield code="c">DE-29</subfield>
      <subfield code="9">r:DE-12</subfield>
      <subfield code="b">ger</subfield>
      <subfield code="d">0030</subfield>
    </datafield>
    <datafield tag="042" ind1=" " ind2=" ">
      <subfield code="a">gnd1</subfield>
    </datafield>
    <datafield tag="043" ind1=" " ind2=" ">
      <subfield code="c">XB-IQ</subfield>
    </datafield>
    <datafield tag="065" ind1=" " ind2=" ">
      <subfield code="a">27.20</subfield>
      <subfield code="2">sswd</subfield>
    </datafield>
    <datafield tag="075" ind1=" " ind2=" ">
      <subfield code="b">b</subfield>
      <subfield code="2">gndgen</subfield>
    </datafield>
    <datafield tag="075" ind1=" " ind2=" ">
      <subfield code="b">kiz</subfield>
      <subfield code="2">gndspec</subfield>
    </datafield>
    <datafield tag="079" ind1=" " ind2=" ">
      <subfield code="a">g</subfield>
      <subfield code="q">s</subfield>
    </datafield>
    <datafield tag="110" ind1="2" ind2=" ">
      <subfield code="a">Al-Adudi Hospital</subfield>
    </datafield>
    <datafield tag="410" ind1="2" ind2=" ">
      <subfield code="a">˜al-œ Bīmāristān al-ʿAḍudī</subfield>
    </datafield>
    <datafield tag="410" ind1="2" ind2=" ">
      <subfield code="a">˜al-œ ʿAḍudī-Krankenhaus</subfield>
    </datafield>
    <datafield tag="410" ind1="2" ind2=" ">
      <subfield code="a">al-ʿAḍudī-Krankenhaus</subfield>
    </datafield>
    <datafield tag="410" ind1="2" ind2=" ">
      <subfield code="a">Adudi Hospital</subfield>
    </datafield>
    <datafield tag="410" ind1="2" ind2=" ">
      <subfield code="9">U:Arab</subfield>
      <subfield code="9">L:ara</subfield>
      <subfield code="a">البيمارستان العضدي</subfield>
    </datafield>
 </record>

Aber wie kann ich dem xml sagen, hol mir die datafield tag =035 mit code a und datafield tag=040 mit code a. Das was ich mache, das ist Zählen, was falsch ist. In dem 1 record kann datafield 410 als 5 Datensatz stehen, in einem anderen als 4. Deshalb, was ich mache es ist falsch. Wer kann mir helfen so die Abfrage formulieren, wo auch where Fkt funktioniert. Bitte!

[defo2]

Vielleicht hast Du das übersehen:
Kannst du bitte noch mal das Eregbis des Statements liefern?

Und als Idee:
Du machst auf dieser Ebene keine gezielten Zugriffe, Du gibst nur einzeln die Felder/Spalten aus, ohne Einschränkung.
Alles andere kommt danach.