XML verarbeiten - insert node

[FlexGer]

Hallo zusammen,

hoffe ihr könnt mir bei einem Problem helfen.

Es geht um eine Massendatenverarbeitung von XML Spalten.

Beispiel XML:
<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
</root>
</xml>

Natürlich ein sehr vereinfachtes Beispiel, aber folgende Aufgabe besteht, die via SQL / PLSQL zu lösen ist, also wohl mit XPATH, XQUERY.
-> Mitarbeiter mit Tag vorname suchen, den Text nehmen (Bspw. 3 beim Mitarbeiter "HIJ"), in eine SQL Funktion werfen und den Output in ein neues Attribut schreiben.

Habt ihr eine Idee?

Vielen Dank im Voraus.

[Christian Marquardt]

https://docs.oracle.com/cd/B19306_01...9/xdb04cre.htm

[FlexGer]

Danke für den Link Christian, aber das Netz habe ich schon nach einer Lösung durchforscht.

[Wernfried]

Die Aufgabe ist nicht ganz klar, bzw. mehrdeutig. Was ist mit "in eine SQL Funktion werfen und den Output in ein neues Attribut schreiben" gemeint?
Was ist den das "neue Attribut"?




Zum ersten Teil der Aufgabe (Mitarbeiter mit Tag vorname suchen) kannst du eine Abfrage wie diese verwenden:

WITH in_data AS 
	(SELECT XMLTYPE('<xml>
	<root>
	<mitarbeiter name="ABC">1</mitarbeiter>
	<mitarbeiter name="DEF">2</mitarbeiter>
	<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
	</root>
	</xml>') AS X FROM dual)
SELECT MITARBEITER_NAME, MITARBEITER_VORNAME, MITARBEITER_ID
FROM in_data 
	NATURAL JOIN XMLTABLE('/xml/root/mitarbeiter[@vorname]' PASSING X COLUMNS 
		MITARBEITER_NAME VARCHAR2(50) PATH '@name',
		MITARBEITER_VORNAME VARCHAR2(50) PATH '@vorname',
		MITARBEITER_ID NUMBER PATH 'text()'
	) e;

Wenn du nur ein einzelnes Attribut haben möchtest bietet sich eher XMLQUERY an, z.B.

WITH in_data AS 
	(SELECT XMLTYPE('<xml>
	<root>
	<mitarbeiter name="ABC">1</mitarbeiter>
	<mitarbeiter name="DEF">2</mitarbeiter>
	<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
	</root>
	</xml>') AS X FROM dual)
SELECT XMLQUERY('/xml/root/mitarbeiter/@vorname' PASSING X RETURNING CONTENT) AS MITARBEITER_VORNAME
FROM in_data;

Das Update ist mir wie gesagt nicht ganz klar, aber eine Variante wäre diese hier:

WITH in_data AS 
	(SELECT XMLTYPE('<xml>
	<root>
	<mitarbeiter name="ABC">1</mitarbeiter>
	<mitarbeiter name="DEF">2</mitarbeiter>
	<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
	</root>
	</xml>') AS X FROM dual)
SELECT 
	XMLQUERY('copy $ma := $old_ma modify (replace value of node ($ma/xml/root/mitarbeiter/@vorname) with $NEUER_VORNAME) return $ma'
		PASSING X AS "old_ma",
		'Sir '||UPPER(MITARBEITER_VORNAME) AS NEUER_VORNAME
	RETURNING CONTENT) AS XML_NEU
FROM in_data 
	NATURAL JOIN XMLTABLE('/xml/root/mitarbeiter[@vorname]' PASSING X COLUMNS 
		MITARBEITER_VORNAME VARCHAR2(50) PATH '@vorname'
	) e;

Ich gebe zu, "früher" war das einfacher - die Funktion UPDATEXML ist inzwischen aber "deprecated", d.h. sie funktioniert aber niemand weiss wie lange noch. Mit den alten Funktionen sähe es so aus:

WITH in_data AS 
	(SELECT XMLTYPE('<xml>
	<root>
	<mitarbeiter name="ABC">1</mitarbeiter>
	<mitarbeiter name="DEF">2</mitarbeiter>
	<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
	</root>
	</xml>') AS X FROM dual)
SELECT 
	UPDATEXML(X, '/xml/root/mitarbeiter/@vorname', 'Sir '||UPPER(EXTRACT(X, '/xml/root/mitarbeiter/@vorname')))
FROM in_data;

Gruss

[FlexGer]

Erst einmal vielen Dank Wernfried!

[highlight=sql]
WITH in_data AS
(SELECT XMLTYPE('<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
</root>
</xml>') AS X FROM dual)
SELECT
XMLQUERY('copy $ma := $old_ma modify (replace value of node ($ma/xml/root/mitarbeiter/@vorname) with $NEUER_VORNAME) return $ma'
PASSING X AS "old_ma",
'Sir '||UPPER(MITARBEITER_VORNAME) AS NEUER_VORNAME
RETURNING CONTENT) AS XML_NEU
FROM in_data
NATURAL JOIN XMLTABLE('/xml/root/mitarbeiter[@vorname]' PASSING X COLUMNS
MITARBEITER_VORNAME VARCHAR2(50) PATH '@vorname'
) e;
[/highlight]

Das Beispiel von dir sieht schon toll aus und geht stark in die richtige Richtung.
Bei mir können mehrere Mitarbeiter mit dem Attribut "vorname" in einem XML vorkommen und auch in verschiedenen Knoten.
Also /xml/root/mitarbeiter ist nicht gegeben, kann man das nach //*[@vorname] umschreiben?
Außerdem möchte ich ein neues Attribut hinzufügen, also beispielsweise name2="Sir XXX".
Sind diese Anpassungen in deinem Beispiel möglich? Vielen Dank.

<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
<bla><mitarbeiter name="KLM" vorname="123">4</mitarbeiter></bla>
</root>
</xml>

Suche alle Knoten mit Attribut vorname, nehme text() vom Knoten, anhand dessen neuen Wert mit SQL Funktion ermitteln und in neues Attribut schreiben.
Wenn die SQL Funktion beispielsweise mit 2 multipliziert sollte das Ergebnis vom XML so aussehen.

<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx" name2="6">3</mitarbeiter>
<bla><mitarbeiter name="KLM" vorname="123" name2="8">4</mitarbeiter></bla>
</root>
</xml>

[Wernfried]

Ja "//*[@vorname]" geht auch.


Für ein neues Attribut sieht die Sache in etwa so aus. Anstatt "'Sir '||UPPER(MITARBEITER_VORNAME) AS NEUER_VORNAME" machst du ungefähr:

 
XMLELEMENT("mitarbeiter", XMLATTRIBUTES(MITARBEITER_NAME d AS "name", MITARBEITER_VORNAME as "vorname", 8 as "name2"), MITARBEITER_ID) AS NEUER_MITARBEITER,
XMLELEMENT("bla", XMLELEMENT("mitarbeiter", XMLATTRIBUTES(MITARBEITER_NAME d AS "name", MITARBEITER_VORNAME as "vorname", 8 as "name2"), MITARBEITER_ID)) AS NEUER_BLA_MITARBEITER

Und im XMLQUERY string steht dann z.B.:

 
XMLQUERY('copy $ma := $old_ma modify (replace node ($ma/xml/root/mitarbeiter) with $NEUER_MITARBEITER) return $ma'

Dir wird nicht viel anders übrig bleiben als dich in die (zugegeben etwas gewöhnungsbedürftige Syntax) hineinzuarbeiten: XQuery Update Facility 1.0

Gruss

[FlexGer]

Vielen Dank Wernfried.

Ich habe schon ein paar Tage mit testen und recherchieren verbraten, mir fällt leider nichts mehr ein, daher wende ich mich hier auch an euch.
Dein Einsatz von replace node und XMLELEMENT setzt den gleichen Aufbau voraus oder? Ich kenne nicht alle Benennungen und Attribute der Knoten.
Sicher kann ich nur sein, die Knoten mit dem Attribut "vorname" geändert werden sollen.
Ich habe aktuell eine Lösung mit PL/SQL, allerdings gehe ich in einer Schleife über alle Knoten mit "vorname" und führe INSERTCHILDXML aus. Im Anschluss wird das XML ausgetauscht.
Allerdings ist dieser Weg zu langsam, ich müsste mit weniger Schritten das XML updaten.

Ein Beispiel was ich gefunden habe um ein Attribut hinzuzufügen:
for $e in doc("employees.xml")//employee
where $e/@manager=true()
return
copy $emp := $e
modify (
replace value of node $emp/salary with "" ,
insert nodes (attribute xsi:nil {"true"})
into $emp/salary
)
return $em

Ist meine Anforderung damit realisierbar?

[Wernfried]

Ist meine Anforderung damit realisierbar?

Keine Ahnung da du deine Anforderung nur sehr rudimentär beschreibst.

Kleiner Tipp, nimm ruhig die "alten" Funktion wie INSERTCHILDXML, XMLELEMENT, UPDATEXML, etc. Für den Anfang sind die leichter zu benutzen.

Gruss

[FlexGer]

Danke Wernfried.
Ja stimmt, mit denen komme ich besser zurecht, allerdings bekomme ich das XML Update leider nicht ohne PL/SQL Loop hin...
Versuche es nochmal besser zu erläutern.

Quell-XML:
[highlight=XML]
<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
<bla><mitarbeiter name="KLM" vorname="123">4</mitarbeiter></bla>
<chef name="GGG" vorname="hffhh">5</chef>
</root>
</xml>
[/highlight]

SQL Statement was noch nicht alles abbildet:
[highlight=sql]
WITH data AS
(SELECT xmltype('
<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
<bla><mitarbeiter name="KLM" vorname="123">4</mitarbeiter></bla>
<chef name="GGG" vorname="hffhh">5</chef>
</root>
</xml>') AS x
FROM dual
)
SELECT XMLQuery('copy $tmp := $XMLVAL modify
(for $i in $tmp//*[@name2][text()=$TESTVAL]/@name2
return replace value of node $i with $NEU)
return $tmp' PASSING x AS xmlval, NEU AS NEU, TESTVAL AS TESTVAL RETURNING CONTENT)
FROM
(SELECT insertchildxml(x, '//*[@vorname]', '@name2', 'emptyvalue') AS x
FROM data
) ,
(SELECT '1111' AS NEU, '4' AS TESTVAL FROM dual
);
[/highlight]

Das Ergebnis:
[highlight=XML]
<xml>
<root>
<mitarbeiter name="ABC">1</mitarbeiter>
<mitarbeiter name="DEF">2</mitarbeiter>
<mitarbeiter name="HIJ" vorname="xxx" name2="emptyvalue">3</mitarbeiter>
<bla><mitarbeiter name="KLM" vorname="123" name2="1111">4</mitarbeiter></bla>
<chef name="GGG" vorname="hffhh" name2="emptyvalue">5</chef>
</root>
</xml>
[/highlight]

Aufgabe zusammengefasst: Pro Knoten mit @vorname ein neues Attribut "name2" anlegen und mit überarbeiteten Wert aus text() füllen.

Erläuterung aktueller Test:
Die Idee hinter dem SQL Statement ist, dass ich via insertchildxml ein dummy Attribut anlege. --> Habe keinen anderen Weg erfolgreich testen können
Danach wird in einer XML Loop nach Knoten mit @name2 gesucht, inklusive Bedingung auf text() vom Knoten.
Der Attributwert soll dann mit dem neuen Wert überschrieben werden.

Ich habe eine Tabelle mit dem alten und neuen Wert.

3	saasd
4	1111
5	sssss

Also möchte ich beim Mitarbeiter "4", in das Attribut name2 den Wert 1111 einfügen.

Vielen Dank für die Mühe.

P.S.
Ein funktionierendes Beispiel zum Hinzufügen von einem Attribut via XQUERY würde mir auch sehr weiterhelfen, da ich damit die Laufzeit meiner PL/SQL Implementierung verbessern könnte.

[Wernfried]

Stimmt mit "replace value of node", kannst du kein neues Attribut erzeugen, du musst das komplette Element (also "<chef>", bzw. "mitarbeiter") neu erzeugen und einfügen, bzw. ersetzten mit "replace node ...".

Wenn das Element verschieden sein kann (also mal "chef" und mal "mitarbeiter" heisst) wird es kompliziert, denke ich.

Anderer Ansatz: Versuche das gesamte XML als Tabelle darzustellen, im Ansatz habe ich das schon beschrieben.

 
CREATE VIEW V_XML
WITH in_data AS 
	(SELECT XMLTYPE('<xml>
	<root>
	<mitarbeiter name="ABC">1</mitarbeiter>
	<mitarbeiter name="DEF">2</mitarbeiter>
	<mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
   <bla><mitarbeiter name="KLM" vorname="123">4</mitarbeiter></bla>
   <chef name="GGG" vorname="hffhh">5</chef>	
	</root>
	</xml>') AS X FROM dual)
SELECT MITARBEITER_NAME, MITARBEITER_VORNAME, MITARBEITER_ID, MITARBEITER_TYPE, MITARBEITER_PARENT_TYPE
FROM in_data 
	NATURAL JOIN XMLTABLE('/xml/*//mitarbeiter|/xml/*//chef' PASSING X COLUMNS 
		MITARBEITER_NAME VARCHAR2(50) PATH '@name',
		MITARBEITER_VORNAME VARCHAR2(50) PATH '@vorname',
		MITARBEITER_ID NUMBER PATH '/text()',
		MITARBEITER_TYPE VARCHAR2(50) PATH 'name()',
		MITARBEITER_PARENT_TYPE VARCHAR2(50) PATH '../name()'
	) e;

Dann baust du das ganze XML neu zusammen, im Ansatz z.B. so:

 
SELECT 
	XMLELEMENT("xml", 
		XMLELEMENT("root", 
			XMLAGG(
				NVL2(MITARBEITER_VORNAME,
					XMLELEMENT(EVALNAME MITARBEITER_TYPE, 
						XMLATTRIBUTES(MITARBEITER_NAME AS "name", MITARBEITER_VORNAME AS "vorname", TABELLE_NEUE_WERTE.neuer_wert AS "name2")),
					XMLELEMENT(EVALNAME MITARBEITER_TYPE, 
						XMLATTRIBUTES(MITARBEITER_NAME AS "name", MITARBEITER_VORNAME AS "vorname")) 
					)
				)				
			)
		)
FROM V_XML
	JOIN TABELLE_NEUE_WERTE ON ...;

Gruss
Wernfried

[FlexGer]

Vielen Dank Wernfried, das ist ein interessanter Ansatz.
Ich teste mal ein bisschen

[jum]

Hilft Dir INSERT NODE ATTRIBUTE evtl . weiter (SELECT vereinfacht)?

WITH DATA AS
  (SELECT xmltype(' 
<xml>
  <root>
    <mitarbeiter name="ABC">1</mitarbeiter>
    <mitarbeiter name="DEF">2</mitarbeiter>
    <mitarbeiter name="HIJ" vorname="xxx">3</mitarbeiter>
    <bla><mitarbeiter name="KLM" vorname="123">4</mitarbeiter></bla>
    <chef name="GGG" vorname="hffhh">5</chef>
  </root>
</xml>') AS x FROM dual)
SELECT XMLQuery('copy $tmp := . modify            
  (for $i in $tmp//*/mitarbeiter[@vorname]
     return insert node attribute name2{$NEU} into $i  
   )             
return $tmp' 
PASSING x ,'vnameneu' AS neu RETURNING CONTENT) xq
FROM DATA;

XQ
------------------------------------------------------------------------------
<xml>
  <root>
    <mitarbeiter name="ABC">1</mitarbeiter>
    <mitarbeiter name="DEF">2</mitarbeiter>
    <mitarbeiter name="HIJ" vorname="xxx" name2="vnameneu">3</mitarbeiter>
    <bla>
      <mitarbeiter name="KLM" vorname="123" name2="vnameneu">4</mitarbeiter>
    </bla>
    <chef name="GGG" vorname="hffhh">5</chef>
  </root>
</xml>

[Wernfried]

"insert node attribute" kannte ich bisher nicht - Liegt wohl daran, dass ich XQuery nur sehr selten benutze.

Gruss
Wernfried

[FlexGer]

Ach so muss man die Syntax schreiben, ja hilft mir sehr, vielen Dank