XML mit Multirows - konvertieren in eine Exceldatei mit mehren Arbeitsblättern

[n8lauscher]

Hallo Zusammen,

gibt es hier XML-Experten?
Ich habe eine XML-Datei mit insgesamt 10000 Datensätzen (Blogposts).
Der Primärschlüssel pro Blogpost ist immer die ist Artikelnummer.

Code:

<?xml version="1.0" encoding="utf-8"?>
<blog>
    <blogpost>
        <Artikelnummer>0001</Artikelnummer>
        <thema>Marke_1</thema>
        <Title>blogttitel_1</Title>
        <kategorie>Kategorie_1</kategorie>
        <beschreibung>Beschreibungstext</beschreibung>
        <wortzaehler>10</wortzaehler>
        <Rating>5</Rating>
        <anzahl_1>0</anzahl_1>
        <anzahl_2>499,00</anzahl_2>
        <anzahl_3>0%</anzahl_3>
        <anzahl_4>0</anzahl_4>
        <anzahl_5>0</anzahl_5>
        <ElternArtikel>0004</ElternArtikel>
        <verfügbar>True</verfügbar>
        <saison>False</saison>
        <Thumbnail>https://thumbnail1.jpg</Thumbnail>
        <blog-URL>https://produktlink_1</blog-URL>
        <bilder>
            <URL>https://bild1.jpg</URL>
            <URL>https://bild2.jpg</URL>
            <URL>https://bild3.jpg</URL>
            <URL>https://bild4.jpg</URL>
            <URL>https://bild5.jpg</URL>
            <URL>https://bild6.jpg</URL>
            <URL>https://bild7.jpg</URL>
            <URL>https://bild8.jpg</URL>
            <URL>https://bild9.jpg</URL>
        </bilder>
        <besonderheiten>
            <Description>
                besonderheiten-text_1

            </Description>
            <Description>
                besonderheiten-text_2

            </Description>
            <Description>
                besonderheiten-text_3

            </Description>
            <Description>
                besonderheiten-text_4

            </Description>
            <Description>
                besonderheiten-text_5

            </Description>
        </besonderheiten>
        <merkmale>
            <Description>variabler merkmale name 1=merkmal_wert_1</Description>
            <Description>variabler merkmale name 2=merkmal_wert_2</Description>
            <Description>variabler merkmale name 3=merkmal_wert_3</Description>
            <Description>variabler merkmale name 4=merkmal_wert_4</Description>
            <Description>variabler merkmale name 5=merkmal_wert_5</Description>
            <Description>variabler merkmale name 6=merkmal_wert_6</Description>
            <Description>variabler merkmale name 7=merkmal_wert_7</Description>
            <Description>variabler merkmale name 8=merkmal_wert_8</Description>
            <Description>variabler merkmale name 9=merkmal_wert_9</Description>
            <Description>variabler merkmale name 10=merkmal_wert_10</Description>
            <Description>variabler merkmale name 11=merkmal_wert_11</Description>
            <Description>bewertungs-rang=Nr. 10 in Thema_1 Nr. 70 in Testkategorie_produkte_1 &gt; Testunterkategorie_produkte_2 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_4</Description>
        </merkmale><BlogDetails/>
        <ranking>12924</ranking><UPC/><EAN/>
        <neu>True</neu>
        <ElternArtikel>1521</ElternArtikel>
        <Reviews>
            <Review>
                <Author>Autor_1</Author>
                <Title>Bewertungstitel_1</Title>
                <Rating>5</Rating>
                <Content>Bewertungstext_1</Content>
            </Review>
            <Review>
                <Author>Autor_2</Author>
                <Title>Bewertungstitel_2</Title>
                <Rating>4</Rating>
                <Content>Bewertungstext_2</Content>
            </Review>
            <Review>
                <Author>Autor_3</Author>
                <Title>Bewertungstitel_3</Title>
                <Rating>3</Rating>
                <Content>Bewertungstext_3</Content>
            </Review>
            <Review>
                <Author>Autor_4</Author>
                <Title>Bewertungstitel_4</Title>
                <Rating>2</Rating>
                <Content>Bewertungstext_4</Content>
            </Review>
            <Review>
                <Author>Autor_5</Author>
                <Title>Bewertungstitel_5</Title>
                <Rating>1</Rating>
                <Content>Bewertungstext_5</Content>
            </Review><Review/>
            <Review>
                <Author>Autor_7</Author>
                <Title>Bewertungstitel_7</Title>
                <Rating>3</Rating>
                <Content>Bewertungstext_7</Content>
            </Review>
            <Review>
                <Author>Autor_8</Author>
                <Title>Bewertungstitel_8</Title>
                <Rating>5</Rating>
                <Content>Bewertungstext_8</Content>
            </Review>
        </Reviews>
    </blogpost>
</blog>

Wenn ich diese in Excel als XML importiere werden die Daten nicht wie gewünscht dargestellt. 

Im Anhang habe ich einmal das daraus zu erstellende XLXS hinzugefügt. 
Die Beispiel-Ergebnis-Datei ist mit dem Datensatz aus dem XML-File befüllt.
Die Daten kommen immer im gleichen Format. 
Es kommt vor, dass nicht immer alle Notes befüllt sind. 
Wenn leer dann immer mit

Code:

<notes/>

multirows gibt es:

Bilder

Code:

        <bilder>
            <URL>https://bild1.jpg</URL>
            <URL>https://bild2.jpg</URL>
            <URL>https://bild3.jpg</URL>
            <URL>https://bild4.jpg</URL>
            <URL>https://bild5.jpg</URL>
            <URL>https://bild6.jpg</URL>
            <URL>https://bild7.jpg</URL>
            <URL>https://bild8.jpg</URL>
            <URL>https://bild9.jpg</URL>
        </bilder>

Besonderheiten

Code:

        <besonderheiten>
            <Description>
                besonderheiten-text_1

            </Description>
            <Description>
                besonderheiten-text_2

            </Description>
            <Description>
                besonderheiten-text_3

            </Description>
            <Description>
                besonderheiten-text_4

            </Description>
            <Description>
                besonderheiten-text_5

            </Description>
        </besonderheiten>

Merkmale (siehe Besonderheit unten)

Code:

        <merkmale>
            <Description>variabler merkmale name 1=merkmal_wert_1</Description>
            <Description>variabler merkmale name 2=merkmal_wert_2</Description>
            <Description>variabler merkmale name 3=merkmal_wert_3</Description>
            <Description>variabler merkmale name 4=merkmal_wert_4</Description>
            <Description>variabler merkmale name 5=merkmal_wert_5</Description>
            <Description>variabler merkmale name 6=merkmal_wert_6</Description>
            <Description>variabler merkmale name 7=merkmal_wert_7</Description>
            <Description>variabler merkmale name 8=merkmal_wert_8</Description>
            <Description>variabler merkmale name 9=merkmal_wert_9</Description>
            <Description>variabler merkmale name 10=merkmal_wert_10</Description>
            <Description>variabler merkmale name 11=merkmal_wert_11</Description>
            <Description>bewertungs-rang=Nr. 10 in Thema_1 Nr. 70 in Testkategorie_produkte_1 &gt; Testunterkategorie_produkte_2 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_4</Description>
        </merkmale>

Besonderheit
Steht immer im Merkmal-blog - heißt immer: bewertungs-rang und kann nicht nur ein Ergebnis enthalten und wird immer als bewertungs-rang kommen. 
Dann kommt immer Rangnummer Nr. xx getrennt durch " in " und dann der Name der Rangkategorie
wenn es einen zusätzlichen Rang gibt - dann wieder: Rangnummer Nr. xx getrennt durch " in " und dann der Name der Rangkategorie

Code:

        <merkmale>
            <Description>bewertungs-rang=Nr. 10 in Thema_1 Nr. 70 in Testkategorie_produkte_1 &gt; Testunterkategorie_produkte_2 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_3 &gt; Testunterkategorie_produkte_4</Description>
        </merkmale>

reviews

Code:

        <Reviews>
            <Review>
                <Author>Autor_1</Author>
                <Title>Bewertungstitel_1</Title>
                <Rating>5</Rating>
                <Content>Bewertungstext_1</Content>
            </Review>
            <Review>
                <Author>Autor_2</Author>
                <Title>Bewertungstitel_2</Title>
                <Rating>4</Rating>
                <Content>Bewertungstext_2</Content>
            </Review>
            <Review>
                <Author>Autor_3</Author>
                <Title>Bewertungstitel_3</Title>
                <Rating>3</Rating>
                <Content>Bewertungstext_3</Content>
            </Review>
            <Review>
                <Author>Autor_4</Author>
                <Title>Bewertungstitel_4</Title>
                <Rating>2</Rating>
                <Content>Bewertungstext_4</Content>
            </Review>
            <Review>
                <Author>Autor_5</Author>
                <Title>Bewertungstitel_5</Title>
                <Rating>1</Rating>
                <Content>Bewertungstext_5</Content>
            </Review><Review/>
            <Review>
                <Author>Autor_7</Author>
                <Title>Bewertungstitel_7</Title>
                <Rating>3</Rating>
                <Content>Bewertungstext_7</Content>
            </Review>
            <Review>
                <Author>Autor_8</Author>
                <Title>Bewertungstitel_8</Title>
                <Rating>5</Rating>
                <Content>Bewertungstext_8</Content>
            </Review>
        </Reviews>

Wer kann mir dabei helfen? - ich würde mich sehr über eine Unterstützung freuen!

Danke im Voraus!

[schauan]

Hi Michael,

mal unabhängig davon wer da helfen könnte oder nicht, wäre sicher die XML (oder eine Beispiel-XML) von Vorteil, die neben "ordentlichen" Ergebnissen auch Problembeispiele enthält. Ein Hinweis, welche Datensätze korrekt importiert werden und welche nicht, wäre sicher auch von Vorteil. Damit könnte ein Helfer testen.

[Flotter Feger]

Hallo,

habe gestern selbst Nachhilfe in XML bekommen ... Danke schön Ralf. :17: 

Schau mal hier https://www.tabellenexperte.de/von-excel...d-zurueck/

Du könntest mal im Directory deines Blocks schauen, ob es bereits eine solche XSD-Datei gibt, die zu deiner XML passt.

[n8lauscher]

Hallo Sabina,

ja das mit XSD hatte ich auch bereits gelesen.
Aber nein - es gibt diese Datei leider nicht.

[maninweb]

Hallo,

ab zum Beispiel Excerl 2016 (was Du in Deinem Profil angegeben hast), kannst Du Power Query verwenden, um XML-Dateien einzulesen.
Anbei eine Beispieldatei, wie sowas aussehen kann: Daten / Abfragen und Verbindungen » Aufgabenbereich, Doppelklick auf Blogpost.
Anschließend das Zahnrad bei Quelle anklicken und den Pfad zur XML-Datei anpassen.

Ich habe mal ein paar Abfragen nur rudimentär gemacht, sozusagen als Anregungen für Dich. Hat die XML-Datei z.B.mehr Blogposts-Knoten,
müssten da Anpassungen erfolgen. Die anderen Abfragen (ausser blogposts) sind sogenannte Verweise, die als Eltern die Blogpost-Abfrage
haben, also sowas wie Unterabfragen.

Die XML-Datei hatte ich mir aus Deinem Post rauskopiert. Zudem ist diese meiner Anischt nach nicht sauber implementier, denn das
Tag ElternArtikel kommt zweimal vor, was in der Abfrage 2 Zeilen macht.

Gruß

[n8lauscher]

@maninweb 

ja vielen Dank.
Das war mir allerdings bereits bekannt. 
Das Problem ist aber einfach - dass Du in PowerQuery nicht auf diese Tabellen von den Merkmalen zugreifen kannst und auch nicht vernünftig auf die Reviews.
Diese werden nicht angezeigt bzw - ist Excel nicht in der Lage dort einen Bezug zu setzen. 

oder anders - ich bin nicht in der Lage diese ggf. damit zu verarbeiten. 

Also:
base
bilder
besonderheiten

geht mit Powerquery outofbox.

Aber dann?
ich komme einfach mit diesen "tables" nicht weiter. 
Ich kann mir in der Powerqery-Ansicht jeden einzelnen Inhalt der Tabelle anzeigen lassen - dann aber immer nur für einen Artikel!?
siehe - Bild Anhang. 

Aber wie kann ich daraus eine Tabelle transformieren?

blogid;merkmale
1;variabler merkmale name 1=merkmal_wert_1
2;variabler merkmale name 2=merkmal_wert_2
3;variabler merkmale name 3=merkmal_wert_3
4;variabler merkmale name 4=merkmal_wert_4
5;variabler merkmale name 5=merkmal_wert_5
6;variabler merkmale name 6=merkmal_wert_6
usw.

das würde ja schon reichen

Aber ich bekomme es nicht hin...

[maninweb]

Hallo,

ich habe den Eindruck, als hättest Du bei Expandieren der Tabellen auf Table geklickt, wo Du dann zu der von Dir gezeigten Ansicht kommst.
Klicke mal auf die Pfeile in der Überschrift (siehe Bild), um die Spalten zu expandieren.

Ausserdem erscheint mir Deine Vorgabe, was die Spalten und Zeilen in den Tabellen betrifft, nicht optimal bzw. auch in Power Query umständlich
und nicht ohne M-Code-Programmierung umzusetzen. Einfacher sind tatsächlich die Listendarstellungen - wo Du dann ggf. per Excel Formeln die
Umstellung machen könntest. PQ-Tabellen sind dynamisch, also ggf. intelligente Tabellen in Excel verwenden.

Anbei mal eine modifizierte Bespieldatei + eine XML-Datei mit simulierten 2 Artikeln. Mehr kann ich leider aktuell für Dich nicht tun, da Deine
Wünsche schon mehr Zeit bräuchten und m.E. über eine Forenhilfe hinaus gehen.

Hinweis: test.txt in test.xml umbenennen (Forum hier mag kein XML) und in der Beispieldatei wieder den Pfad zur XML anpassen.

Gruß

[n8lauscher]

@maninweb

Dein Ergebnis ist schon mal sehr gut!
Ich werde dies jetzt einmal mit einem kleineren "real-Datensatz" testen. 
Danke für Deine Mühe und Anregungen. 

Deinen Einwand nach dem PowerQuery die Daten zu tanspondieren halte ich auch für sinnvoll. 
Ich muss mir jetzt erst einmal angucken wie du es geschafft hast die Merkmale und Reviews auszulesen. 
Das hat mich wirklich verrückt gemacht. 

Danke für Deine Mühe.

[n8lauscher]

noch eine Frage habe ich.
Stellt Euch vor, ich habe ca. 30 XML- Datensätze.
Jeder Datensatz steht für eine Blog-Kategorie.
Und jedes XML ist in einem eigenen " Kategorie " Verzeichnis gespeichert. 
Die XML-Files sind alle so wie oben beschrieben aufgebaut.

Also so:

c:\Daten\Blogkategorie_1\komplett.xml
c:\Daten\Blogkategorie_2\komplett.xml
c:\Daten\Blogkategorie_3\komplett.xml
c:\Daten\Blogkategorie_4\komplett.xml
c:\Daten\Blogkategorie_5\komplett.xml

Ich möchte dann später daraus entsprechend für jedes XML ein XLSX speichern:
c:\Daten\Blogkategorie_1\bearbeitet.xlsx
c:\Daten\Blogkategorie_2\bearbeitet.xlsx
c:\Daten\Blogkategorie_3\bearbeitet.xlsx
c:\Daten\Blogkategorie_4\bearbeitet.xlsx
c:\Daten\Blogkategorie_5\bearbeitet.xlsx

Wie bekomme ich es jetzt am sinnvollsten hin - so dass ich ggf. nur eine MusterAbfrage - Datei erstellen muss - und diese dann später nur als bearbeitet und dann in den entsprechenden Ordner lege?
d.h. der Pfad von wo sich die XML gezogen werden soll - müsste bei dem Muster bevor das Query ausgeführt wird - abgefragt werden?

Habt Ihr hier einen Tipp für mich?

DANKE

[maninweb]

Hallo,

Variabler Pfad? Dann wäre das Stichwort Parameter in Power Query. Im PQ-Editor zu finden/erstellen unter Parameter verwalten.
Man kann auch eine Zelle als Parameter auslesen. Dazu googlen (ggf. in Englisch power query parameter from cell).

Gruß