Create XML File

Question

Hallo zusammen!

Ich habe bereits eine neue Frage. Ich habe zwar vergleichbares bereits gefunden: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/29d607e2-fc5b-48a0-bee1-a8319dda7ead nur passt die Antwort leider nicht zu meinem Problem und ich habe Schwierigkeiten dies auf meine Bedürfnisse anzupassen, daher bin ich erneut auf Eure Hilfe angewiesen. In dem anderen Forum wird das Thema anscheinend nicht mehr beachtet, da eigentlich beantwortet und alt...

Ich möchte eine XML Datei erstellen (und am liebsten auf der Festplatte speichern, aber das ist im anderen Thema beschrieben)

So soll diese aussehen: (einmal "header" und gant oft "line")

<data>
	<set>
		<header>
			<header_child_1>...</header_child_1>
			<header_child_2>...</header_child_2>
		</header>
		<line>
			<line_child_1>...</line_child_1>
			<line_child_2>...</line_child_2>
		</line>
		<line>
			<line_child_1>...</line_child_1>
			<line_child_2>...</line_child_2>
		</line>
		...
	</set>
</data>

Mein Vorgehen ist so ähnlich:

SELECT
	1 AS Tag,
	NULL 		AS Parent,
	NULL 		AS [data!1!xml],
	NULL 		AS [line!2!line_child_1!element],
	NULL 		AS [line!2!line_child_2!element]

FROM my_table c
where c.Id = 1

Union all

SELECT
	2 		AS Tag,
	1 		AS Parent,
	NULL 		AS [data!1!xml],
	'value1' 	AS [line!2!line_child_1!element],
	'value2' 	AS [line!2!line_child_2!element]


FROM my_table c
where c.Id = 1

FOR XML EXPLICIT

Allerdings habe ich Schwierigkeiten, genau die Form zu erzeugen, die ich brauche. Erstens kann ich nicht so viele Kinder in die "Tiefe" erzeugen (habe nur 2 Ebenen hinbekommen) und zweitens kann ich keine Elemente auf gleicher Ebene erzeugen, wie "header" und "line".

 

Bin wirklich schon seit Stunden und (fast) Tagen an dem Problem dran. Bin für jeden Hinweis sehr dankbar!

Answer 1

Ich habe zwei Ideen, scheitere aber bislang bei beiden.

Idee 1: Einer Variable @varHeader wird der Teil des Codes zugewiesen, der für die Erstellung des Teilbaumes mit dem Header zuständig ist. Eine anderen Variable @varLine der andere Teil. Anschließend folg der Code, der Quasi das Obergerüst erstellt mit "data" und "set"... Das Problem ist, dass man Folgendes anscheinend nicht machen kann:

Select @varHeader(
SELECT
	1 AS Tag,
	NULL 		AS Parent,
	NULL 		AS [data!1!xml],
	NULL 		AS [line!2!line_child_1!element],
	NULL 		AS [line!2!line_child_2!element]

FROM my_table c
where c.Id = 1

Union all

SELECT
	2 		AS Tag,
	1 		AS Parent,
	NULL 		AS [data!1!xml],
	'value1' 	AS [line!2!line_child_1!element],
	'value2' 	AS [line!2!line_child_2!element]


FROM my_table c
where c.Id = 1

FOR XML EXPLICIT
)

kann das irgendwie umgangen werden? Hätte ich die beiden Teile in zwei Variablen, könnte ich mit einem ähnlichen Code an der richtigen Stelle einfach die beiden Variablen einfügen. Ähnlich wie:

set @header = '<header>
			<header_child_1>...</header_child_1>
			<header_child_2>...</header_child_2>
		</header>'
set @line = '<line>
			<line_child_1>...</line_child_1>
			<line_child_2>...</line_child_2>
		</line>'


SELECT
	1 AS Tag,
	NULL 		AS Parent,
	NULL 		AS [data!1!xml],
	NULL 		AS [set!2!header!xml],
	NULL 		AS [set!2!line!xml]

Union all

SELECT
	2 		AS Tag,
	1 		AS Parent,
	NULL 		AS [data!1!xml],
	@header 	AS [set!2!header!xml],
	@line	 	AS [set!2!line!xml]


FOR XML EXPLICIT
GO

Idee 2) geht ähnlich, aber ich erstelle je ein stored procedure für die jeweiligen Teile des XMl Baumes. Anschließend müsste ich den Befehl EXEC nur so aufrufen können, dass die Ausgabe jeweils der richtigen Variable zugewiesen wird. Wenn eine stored procedure z.B. createXMLHeader heisst, wie könnte ich diese aufrufen?

 

Ich muss übrigens mit MS SQL 2000 arbeiten...

 

Für sämtliche Unterstützung bin ich stets sehr dankbar!

Answer 2

Hallo,

bei FOR XML EXPLICT werden Tag / Parent als Parent-Child Relation für die Ebene im XML verwendet. Wenn Du also tiefer schachteln willst, musst Du das bereits "bei den Daten" machen und im Alias entsprechend die Tags definieren. Sprich: Noch einen Tag 3 mit Parent  2.

SELECT

     1      AS Tag,

     NULL   AS Parent,

     NULL   AS [data!1!xml],

     NULL   AS [set!2!header!xml],

     NULL   AS [header!3!header_child1!xml],

     NULL   AS [header!3!header_child2!xml]

UNION ALL

SELECT

     2,

     1,

     NULL,

     'Headern value',

    NULL,

    NULL        

UNION ALL

SELECT

     3,

     2,

     NULL,

     NULL,

    'Header_child 1 value',

    'Header_child 2 value'

FOR XML EXPLICIT

 

Ergebnis:

 

<data>

  <set>

    <header>Headern value</header>

    <header>

      <header_child1>Header_child 1 value</header_child1>

      <header_child2>Header_child 2 value</header_child2>

    </header>

  </set>

</data>

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 3

Perfekt! Vielen Dank!

 

Jetzt noch ein Tipp, wie ich header und line auf gleicher "Tiefe" (siehe Bsp. ganz oben) darstellen kann? Denn, wenn ich Folgendes mache, bekomme ich eine Fehlermeldung:

 

NULL AS [header!3!header_child2!xml],
NULL AS [line!3!line_child2!xml]

Fehlermeldung:

XML tag ID 3 that was originally declared as 'header' is being redeclared as 'line'.

Meine Überlegung war daher, die Inhalte von header und line jeweils in einer Variable zu speichern und dann an den entsprechenden Stellen einzufügen. Nur klappt das leider auch nicht...

Answer 4

Mein Kollege hatte eine 3 alternative Lösung, die funktioniert:

Die XML Datei wird aus einzelnen Strings zusammengesetzt. An den entsprechenden Stellen werden die erforderlichen Werte eingefügt.

Ähnlich wie

set @xmlData = '<data>' + @inhalte + '</data>' ...

Sollte jemand doch noch eine Lösung für die anderen Alternativen haben, wäre ich sehr dankbar dafür!

Answer 5

Klar, das geht auch, in der BOL gibt es bei sp_send_dbmail ein nettes Beispiel, wie man eine Html Seite mit einer Tabelle sich "zusammen selektieren" kann, für XML würde es analog gehen

Du könntest auch statt FOR Explict mit dem PATH Modus experimentieren. Mit SQL Server >= 2005 geht das damit problemlos, nur der 2000 kennt die TYPEAnweisung für XML nicht (die CTEs sowieso nicht), sonst könnte die Lösung so aussehen:

;WITH

headers

AS (SELECT 1 AS HeaderID, 'Header 1' AS HeaderValue

    UNION ALL

    SELECT 2, 'Header 2')

,

lines

AS (SELECT 1 AS HeaderID, 1 AS LineID, 'Line 1-1' AS LineValue

    UNION ALL

    SELECT 1, 2, 'Line 2-1'

    UNION ALL

    SELECT 2, 1, 'Line 1-2'

    UNION ALL

    SELECT 2, 2, 'Line 2-2')

 

SELECT HeaderId AS 'Header/Id'

       ,

       (SELECT LineID AS 'line'

              ,LineValue as 'line/value'

        FROM lines

        WHERE lines.HeaderID = headers.HeaderID

        FOR XML PATH(''),type) AS 'lines'

FROM headers

FOR XML PATH('set'), ROOT('data')

 

<data>

  <set>

    <Header>

      <Id>1</Id>

    </Header>

    <lines>

      <line>1<value>Line 1-1</value></line>

      <line>2<value>Line 2-1</value></line>

    </lines>

  </set>

  <set>

    <Header>

      <Id>2</Id>

    </Header>

    <lines>

      <line>1<value>Line 1-2</value></line>

      <line>2<value>Line 2-2</value></line>

    </lines>

  </set>

</data>

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 6

Hallo Olaf,

vielen Dank an dieser Stelle noch einmal für deine Mühe! Ich musste die XML Datei letztendlich doch noch auf die "einfache" Art und Weise erstellen, wie in meinem letzten Post gezeigt. Leider muss ich bei SQL 2000 bleiben. Danke auf jeden Fall für die vielen Anregungen, werde die, bei Zeiten mal ausprobieren!