ComputerBase Menü
Aktuelles

XML aus Tabellen erstellen

L

LauraM

Ensign
Registriert
Nov. 2019
Beiträge
133
Hallo, ich arbeite in MSSQL und habe ich hier 3 Tabellen angelegt, die ich über pk verknüpfen kann. Ich kann eine xml Datei bauen aufgrund von einer Tabelle, was ich auch gemacht habe.
Code: 
select top 10
kaufnr as '@id',
    'produktnr' as '@type',
    
        --(SELECT name 'v1:name',
        pro1 as 'v1:pro/v1:title',
       pro2 as 'v1:zusatzpro/v1:title'
      from [tab1]
  

FOR XML PATH('v1:produkt'), ROOT('v1:produkts')

Jetzt benötige ich noch andere Daten für meine xml Datei, die sich in anderen Tabellen befinden.
Wie kann ich eine XML bauen, die megreren Tabellen haben. Wie kann ich Verknüpfung zeigen?

Alle Tabellen haben einen kaufnr, über denn kann ich die Tabellen verknüpfen.

Danke im Voraus für eure Hilfe!
 
Wenn ich mir das Beispiel von Microsoft so anschaue, dann sollte es eigentlich kein Problem sein in die Abfrage einfach einen Join einzubauen.
SQL: 
USE AdventureWorks2012
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO;

Hast du das einfach mal probiert?
 
Hi, ich habe sogar mit 3 Tabellen ausprobiert und funktioniert einwandfrei. Danke!
Ergänzung ()

Nun habe ich ein Problem;
es gibt 3 Tabellen:
Code: 
tab 1:
produktnr, pro1, pro2
Code: 
tab 2:
produktnr, tel1
Code: 
tab 3:
produktnr, land

Allerdings wenn ich tab2 abfrage, habe ich manchmal als Ergebnis mehr Datensätze zu einem produktnr:
Code: 
123, 256x
123, 456a
124, 101

mit group by bekomme ich Ergebnis:
Code: 
123, 256x, 456a
124, 101

Nun jetzt frage ich mich, wie kann ich das bei xml aufbauen? Wie kann ich xml so deklarieren, dass wenn zu einem Produktnr mehrere tel1 sind, sollen die Nummer untereinander sein, und nicht wie hier:

Code: 
<journal>
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:title>
    </v1:pros>
      <v1:tels>
      <tel>256x</tel>
    </v1:tels>
    <v1:land>
      <annotation>
        <documentation>DE</documentation>
      </annotation>
    </v1:land>
  </v1:journal>
  <v1:journal id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:title>
    </v1:pros>
      <v1:tels>
      <tel>456a</tel>
    </v1:tels>
    <v1:land>
      <annotation>
        <documentation>DE</documentation>
      </annotation>
    </v1:land>
  </v1:journal>

Es sollte so aussehen:
Code: 
<journal>
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:title>
    </v1:pros>
      <v1:tels>
      <tel>256x</tel>
      </v1:tels>
      <tels1>
      <tel>456a</tel>
    </v1:tels1>
    <v1:land>
      <annotation>
        <documentation>DE</documentation>
      </annotation>
    </v1:land>
  </v1:journal>

An sich möchte ich, bzw, es muss so ein, dass wenn zu einem produkt nr mehr als ein te vorkommt, soll das erste als tel gezeigt werden und die andere als zustztel.
 
Zuletzt bearbeitet:
Hätte jemand Idee, wie ich das abfragen kann?
Ergänzung ()

Bis jetzt mache ich so:

Code: 
USE [ds-result_fis];

select top 100 
    
kaufnr as '@id',
      'produktnr' as '@type',                     
        pro1 as 'v1:pro/v1:title',
       pro2 as 'v1:zusatzpro/v1:title'
       from [tab1]
         FROM [zdb] 
            
            inner join kaufnr 
            on tab1.kaufnr=tab2.kaufnr
          
FOR XML PATH('v1:journal'), ROOT('v1:journals')

Aber dann bekomme ich die XML Datei, wie oben.
 
Zuletzt bearbeitet:
Kann mir jemand helfen, oder nicht? Ich probiere hier mit Case Funktion, aber bekomme ich Fehler etc.

Kann ich noch auf Eure Tipps rechnen?

Vielle Grüße L
 
Stell Mal bitte deine Selects rein, so in Prosa kann ich es mir schlecht vorstellen. Vielleicht fällt mir mit dem eigentlichen Code noch was ein
;)
Ergänzung ()

Sorry, bin blind, steht ja alles da... Schau es mir morgen im Management Studio an!
 
Also, kann mit die Code-Beispiele leider wenig anfangen, die sind glaub ich unvollständig und machen zumindest nicht viel sinn. Auch die XML-Beispiele sind beide unvollständig, Tags werden nicht zugemacht und teilweise nicht mal declariert: z.B. bei <v1:pro>taschenbuch bronze</v1:title> heißen die Tags nicht gleich.

Dann im SQL-Teil:
SQL: 
 from [tab1]
         FROM [zdb]
            
            inner join kaufnr
            on tab1.kaufnr=tab2.kaufnr

2x FROM untereinander geht nicht, und gejoined werden immer Tabellen miteinander, nicht Spalten, so wie es da ausschaut.

Wenn du mal den tatsächlichen Code reinkopierst inklusive deine Datenquellen - also Tabellenaufbau kommen wir die Sache sicher näher..
 
Entschuldigung, ich möchte nicht mit meiner Daten hier arbeiten, deshalb wahrscheinlich habe ich ich hier oder das was falsch geschrieben, es tut mir leid:

Das ist meine Abfrage:

Code: 
USE [ds-result_fis];

select top 100

kaufnr as '@id',
      'produktnr' as '@type',                 
        pro1 as 'v1:pro/v1:title',
       pro2 as 'v1:zusatzpro/v1:title'

       from [tab1]
                
            inner join kaufnr
            on tab1.kaufnr=tab2.kaufnr
      
FOR XML PATH('v1:journal'), ROOT('v1:journals')

Nach der Abfrage bekomme ich die Antwort:

Code: 
<journals>
<journal>
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
      <v1:tels>
      <tel>256x</tel>
    </v1:tels>
     </v1:journal>
  <v1:journal id="010014802">
    <v1pros>
      <v1pros>taschenbuch bronze</v1pro>
    </v1pros>
      <v1:tels>
      <tel>456a</tel>
    </v1:tels>
        </v1:land>
  </v1:journal>
</journals>

Und es soll so aussehen, wenn ich das händisch umschreibe:

Code: 
<journals>
<journal>
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
      <v1:tels>
      <tel>256x</tel>
      </v1:tels>
      <tels1>
      <tel>456a</tel>
    </v1:tels1>
  </v1:journal>
</journals>
Ergänzung ()

Es geht vor allem darum, dass ich nicht weiss, wie ich in der XML Abfrage einbauen soll für folgende Aufgabe:
schau mal, ob zu einem Produkt produktnr ein pro vorhanden ist, wenn nicht vorhanden, geh zu nächsten datensatz, wenn vorhanden tue folgendes:
wenn zu einem Produkt ein pro vorhanden ist, trag als Atributt tels ein, wenn mehr tels vorhanden sind , dann träg die anderen als tels1 ein.

Wissen Sie, was ich meine?
 
Zuletzt bearbeitet:
Du möchtest also die erste TelNr aus tab2 mit ein anderer Tag als die restlichen TelNrn ausgeben?
Ist in tab2 irgendeine Sortierungsmöglichkeit vorhanden? SQL-Server sortiert nämlich nicht immer gleich wenn es nicht explizit mitgegeben wird, somit wäre die erste TelNr nicht immer gleich.
Ergänzung ()

Jetzt überschneiden wir uns :-)

schau mal, ob zu einem Produkt produktnr ein pro vorhanden ist, wenn nicht vorhanden, geh zu nächsten datensatz
Zum Vergrößern anklicken....
Das ist im Prinzip einfach ein Inner Join (ergänzung: bzw. eine Where-Bedingung wenn das deine Hauptquelle ist --> WHERE pro IS NOT NULL oder ähnlich)

wenn zu einem Produkt ein pro vorhanden ist, trag als Atributt tels ein, wenn mehr tels vorhanden sind , dann träg die anderen als tels1 ein.
Zum Vergrößern anklicken....
Ich verstehe was du meinst ja, ich versuch mal was ähnliches zu bauen

P.S. Sie ist nicht notwendig ;-)
 
Zuletzt bearbeitet: (Ergänzung)
Tab2 ist nur so aufgebaut:
Code: 
tab 2:
produktnr, tel1
Ich kann sortieren nach *, oder order by, wie ich oben geschrieben habe:

sie sieht so aus:
Code: 
123  256x
123  456a
124  101
125  102a
125  102b
125  102c
126  250a

wenn ich order by produktnr abfrage habe ich Lösung:
Code: 
123  256x
123  456a
124  101
125  102a
125  102b
125  102c
126  250a

Ich möchte die tab1 und tab2 verknüpfen. in tab 1 sind einzelne Datensätze, also 100. In tab 2 sind mehrere Datensätze 145, da dort manchmal zu einem produktnr mehr tels sind.

Ich möchte so verknüpfen, dass es so aussieht, in Excel z.B:

Code: 
123  256x   456a  null
124  101     null    null
125  102a  102b  102c
126   250a   null    null
 
Es ist nicht genau was du beschrieben hast, aber ich denke, mit ungefähr diese Vorgehensweise wird es gehen:

SQL: 
WITH tab1 AS
(
    SELECT 123 AS produktnr, NULL AS pro
    UNION ALL
    SELECT 234 AS produktnr, 'taschenbuch bronze' AS pro
)
, tab2 AS
(
    SELECT 123 AS produktnr, 4986468633 AS telnr
    UNION ALL SELECT 234 AS produktnr, 468888883 AS telnr
    UNION ALL SELECT 234 AS produktnr, 3168866810 AS telnr
    UNION ALL SELECT 234 AS produktnr, 1588664000 AS telnr
)

SELECT [t1].[produktnr],
       [t1].[pro],
       [t2].*
FROM [tab1] t1
OUTER APPLY (SELECT   IIF([t2].[Zeile] = 1,[t2].[telnr],NULL) AS tels
                    , IIF([t2].[Zeile] > 1,[t2].[telnr],NULL) AS tels1
             FROM (    SELECT [t2].[produktnr], [t2].[telnr], ROW_NUMBER() OVER (PARTITION BY [t2].[produktnr] ORDER BY [t2].[telnr]) AS Zeile
                    FROM [tab2] t2
                    WHERE [t2].[produktnr] = t1.[produktnr]
                ) t2
            FOR XML PATH('')
        ) t2(liste)
WHERE [t1].[pro] IS NOT NULL

Du müsstest auf jeden Fall die Telefonnummern mit ein Apply dazu verknüpfen und schon in der inner Abfrage daraus eine XML-Zeile machen. Du kannst du dann an der richtigen Stelle an deine pros hängen.
Bin leider selbst nicht sehr erfahren in XML-Ausgabe, hoffe also, dass du so weiter kommst. Wenn irgendwas an mein Code nicht klar ist: einfach noch mal melden 👍
 
Oelepoeto schrieb:
Das ist im Prinzip einfach ein Inner Join (ergänzung: bzw. eine Where-Bedingung wenn das deine Hauptquelle ist --> WHERE pro IS NOT NULL oder ähnlich)
Zum Vergrößern anklicken....
Vielen Dank, ich versuche hier case einzubauen, aber ich weiss es nicht wo ...

Code: 
USE [ds-result_fis];

select top 100

kaufnr as '@id',
      'produktnr' as '@type',                 
        pro1 as 'v1:pro/v1:title',
       pro2 as 'v1:zusatzpro/v1:title'

       from [tab1]
                
            inner join kaufnr
            on tab1.kaufnr=tab2.kaufnr
      
FOR XML PATH('v1:journal'), ROOT('v1:journals')

Ich habe Verkettung eingebaut:

Code: 
USE [ds-result_fis];

select top 100

kaufnr as '@id',
      'produktnr' as '@type',                 
        pro1 as 'v1:pro/v1:title',
       pro2 as 'v1:zusatzpro/v1:title',

(select stuff((select ',' + tel1
            from tab2 t2 where tab1.controlfield=t2.controlfield 
            
            for xml path('')),1,1,''))    as  'v1:tels/tel'

       from [tab1]
                
            inner join kaufnr
            on tab1.kaufnr=tab2.kaufnr
      
FOR XML PATH('v1:journal'), ROOT('v1:journals')

[code]

Dann bekomme ich Lösung:
[code]
<journals>
<journal>
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
      <v1:tels>
      <tel>256x, 456a</tel>
      </v1:tels>
        </v1:journal>
</journals>

Was nicht das ist, was ich möchte, leider.
 
Du machst da nach wie vor den inner join auf kaufnr (muss das nicht tab2 sein??). Genau an der Stelle müsste das Konstrukt mit outer apply kommen, sonst ändert sich die Datenstruktur nicht wirklich.
 
Code: 
(
    SELECT 123 AS produktnr, NULL AS pro
    UNION ALL
    SELECT 234 AS produktnr, 'taschenbuch bronze' AS pro
)
, tab2 AS
(
    SELECT 123 AS produktnr, 4986468633 AS telnr
    UNION ALL SELECT 234 AS produktnr, 468888883 AS telnr
    UNION ALL SELECT 234 AS produktnr, 3168866810 AS telnr
    UNION ALL SELECT 234 AS produktnr, 1588664000 AS telnr
)

Sorry, die Zahlen 123, 46888..., was grün gekenzeichnet sind , verstehe ich nicht .... könntest du näher sagen, was das ist?
 
Das sind nur meine Testdaten, also so, wie ich dein Datenstruktur verstanden hab. Die Zahlen sind also fiktive Produktnummer und Telefonnummer..
Ergänzung ()

In deine Abfrage solltest du diese Teile nicht verwenden weil du ja wirklich eine Tabelle tab1 und tab2 hast
 
In meiner Tabellen sind alle Daten als varchar definiert.
Ich bekomme Meldung:
Code: 
Fehler beim Konvertieren des nvarchar-Werts '01027281X' in den int-Datentyp.

wharscheinlich, da das Programm ein int verlangt, stimmts? Ich kann die produktnr nicht als int definieren, da sie auch Buchstaben besitzen.
In der Unterabfrage gibt es per SELECT 1 eine Zahl zurück, deswegen? Wie kann ich das umgehen?


Bevor die Abfrage beginnt, habe ich namenspace noch deklariert:

Code: 
WITH XMLNAMESPACES (
'v3.test.com as commons,
'v1.produkt.com' as v1)

Der Fehler zeigt auf die Zeile 2 was in diesem Fall ist: WITH XMLNAMESPACES (


!!! Ich habe gefunden: wo 1 steht muss man in '' markieren :-)
 
Zuletzt bearbeitet:
Ich glaube, das ist weil nach 'v3.test.com keine Hochkomma folgt.
Beim Konvertieren ist mir nicht ganz klar wo der Fehler liegt. Wie schaut die Abfrage jetzt aus? Kann auch in Kombination mit '+' auftreten, in dem Fall versucht er die Summe zu bilden wenn eine der beiden Werte eine Zahl ist. Eine Alternative ist CONCAT(wert1,wert2)
 
Ich bekomme keinen Fehler!!!
Dort wo kein tel1 vorhanden ist, mach auch auch nicht in xml Datei- der Argument ist nicht vorhanden.
Wenn tel1 vorhanden, dann macht in xml Datei der Argument tel1- aber trägt den Wert nicht ein, aber <issns xmlns=......

Mein code sieht so aus (also deins)


Code: 
WITH XMLNAMESPACES (
'v3.test.com' as commons,
'v1.produkt.com' as v1)


SELECT [t1].[produktnr],
       [t1].[pro],
       [t2].*
FROM [tab1] t1
OUTER APPLY (SELECT   IIF([t2].[zeile] = 1,[t2].[telnr],NULL) AS tels
                    , IIF([t2].[Zeile] > 1,[t2].[telnr],NULL) AS tels1
             FROM (    SELECT [t2].[produktnr], [t2].[telnr], ROW_NUMBER() OVER (PARTITION BY [t2].[produktnr] ORDER BY [t2].[telnr]) AS Zeile
                    FROM [tab2] t2
                    WHERE [t2].[produktnr] = t1.[produktnr]
                ) t2
            FOR XML PATH('')
        ) t2(liste)
WHERE [t1].[pro] IS NOT NULL
FOR XML PATH('v1:journal')
wenn telnr vorhanden ist, mach es so:

Code: 
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
       <tel><issns 'v3.test.com' as commons 'v1.produkt.com' as v1 >5682s <issns> ></tel>
          <v1:journal>
</journals>

Da ich weiss, dass unter dieser numer tel ist, also es soll so aussehn, aber er zieht den ganzen Pfad:
Code: 
<v1:produktnr id="010014802">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
       <tel>5682s</tel>
          <v1:journal>
</journals>


Bitte schau dir das mal an uns sag mir, warum liefert er der Nummer nicht. Ich habe heute graaue Haare schon :-).
 
Zuletzt bearbeitet:
Guten Morgen,

jetzt kann ich ablesen die issn:, ich habe einfach as tels gelöscht und es funktioniert.

Allerdings er liest das zusätzliche tels1 nicht richtig ein.
Es soll so aussehen:
Code: 
<v1:produktnr id="123">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
       <tel>256x</tel>
       <tel>456a</tel>
          <v1:journal>
</journals>

und jetzt sieht so aus:

Code: 
<v1:produktnr id="123">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
       <tel>256x&lt;atels1 xmlns:v1="v3.test.com" xmlns:commons="v1.produkt.com"&gt;456a&lt;/tels1&gt;</tel>
   <v1:journal>
</journals>

Wenn zusätzliche tels, der zieht die xml mit. Wenn ich as tels aus der Abfrage lösche:
Code: 
WITH XMLNAMESPACES (
'v3.test.com' as commons,
'v1.produkt.com' as v1)


SELECT [t1].[produktnr],
       [t1].[pro],
       [t2].*
FROM [tab1] t1
OUTER APPLY (SELECT   IIF([t2].[zeile] = 1,[t2].[telnr],NULL) 
                    , IIF([t2].[Zeile] > 1,[t2].[telnr],NULL) 
             FROM (    SELECT [t2].[produktnr], [t2].[telnr], ROW_NUMBER() OVER (PARTITION BY [t2].[produktnr] ORDER BY [t2].[telnr]) AS Zeile
                    FROM [tab2] t2
                    WHERE [t2].[produktnr] = t1.[produktnr]
                ) t2
            FOR XML PATH('')
        ) t2(liste)
WHERE [t1].[pro] IS NOT NULL
FOR XML PATH('v1:journal')

dann Ergebnis sieht so aus:

Code: 
<v1:produktnr id="123">
    <v1:pros>
      <v1:pro>taschenbuch bronze</v1:pro>
    </v1:pros>
       <tel>256x456a</tel>
   <v1:journal>
</journals>
 
Du musst dich einloggen oder registrieren, um hier zu antworten.

Ähnliche Themen

R
SQL Tool gesucht: ERD aus Tabellen erstellen lassen und Relationen manuell einzeichnen
Antworten
2
Aufrufe
571
Oelepoeto
O
L
SQL XML aus mehreren Tabellen erstellen
Antworten
11
Aufrufe
2.998
LauraM
L
L
Google Tabellen Statistik erstellen
Antworten
1
Aufrufe
1.628
DJMadMax
DJMadMax
J
SQL Tabellen erstellen / überprüfen ob Text enthalten ist /updaten
Antworten
8
Aufrufe
953
Magellan
Magellan
roker002
SQL Mitteilung ob die Tabellen erstellt worden sind.
Antworten
2
Aufrufe
910
roker002
roker002
Zurück
Oben

Willkommen auf ComputerBase!

Mit Werbung weiterlesen

Besuche ComputerBase wie gewohnt mit Werbung und Tracking. Die Zustimmung ist jederzeit widerrufbar.

Details im Privacy Center und in der Liste unserer Partner. Ein Widerruf ist möglich in der Datenschutzerklärung.

… oder ComputerBase Pro bestellen

Nutze ComputerBase ohne Werbebanner, Video-Ads und Werbetracking schon für 4 €/Monat oder 36 €/Jahr.

Mehr zu ComputerBase Pro

Bereits Pro-Nutzer? Hier anmelden.

Tracking: Wir und unsere 160 Partner verarbeiten personenbezogene Daten, indem wir mit auf Ihrem Gerät gespeicherten Informationen (z. B. eindeutige Kennungen in Cookies) ein Nutzungsprofil erstellen, um z. B. Anzeigen zu personalisieren. Verarbeitungszwecke: Genaue Standortdaten und Abfrage von Geräteeigenschaften zur Identifikation, Informationen auf einem Gerät speichern und/oder abrufen, Personalisierte Anzeigen und Inhalte, Anzeigen- und Inhaltsmessungen, Erkenntnisse über Zielgruppen und Produktentwicklungen.

Impressum Kontakt Datenschutz