XML z SQLa - jeden rodzic - wiele wartości


(Grzegorz Pyzera) #1

Wymyśliłem sobie takie proste zapytanie:

declare @xmldoc xml

set @xmldoc = ( select

tabela.Id as '@id', 

tabela.Wartosc as '@Rozmiar'

From tabela

WHERE tabela.PkId IN (1, 2, 6, 9, 13)

FOR XML Path ('Rozmiary'), ROOT ('ROOT'), ELEMENTS, TYPE)

Select @xmldoc

które tworzy mi takiego XMLa:

Jak robię to:

declare @xmldoc xml

set @xmldoc = ( select

tabela.Id as '@id', 

tabela.Wartosc as 'Rozmiar'

From tabela

WHERE tabela.PkId IN (1, 2, 6, 9, 13)

FOR XML Path ('Rozmiary'), ROOT ('ROOT'), ELEMENTS, TYPE)

Select @xmldoc

Wychodzi mi:

100x100


104


104-140


105x105

Co muszę zmienić, żeby mieć takiego XMLa?:

100x100
104
104-140
105x105

(Tomek Matz) #2
DECLARE @xmldoc XML

SET @xmldoc = (

	SELECT

	   tabela.Id as 'Rozmiar/@id',

	   tabela.Wartosc as 'Rozmiar'

	FROM tabela

	WHERE tabela.PkId IN (1, 2, 6, 9, 13)

	FOR XML PATH(''), ROOT('Rozmiary')

)

SELECT @xmldoc

FOR XML PATH(''), ROOT('Root')

Możliwość generowania kodu XML przy użyciu T-SQL to ciekawa funkcjonalność. Wcześniej nie miałem okazji z niej korzystać, ale na pewno kiedyś się przyda.


(Grzegorz Pyzera) #3

Już otrzymałem odpowiedź na innym forum ale dzięki za zainteresowanie :slight_smile: Cała sprawa się rozbiła o to, że nie przyszło mi do głowy że PATH może być puste :smiley:

Ale to i tak łatwiejsza połowa tego co muszę zrobić.. :roll:


(Tomek Matz) #4

Może być puste, ale tylko pod warunkiem, że nie próbujesz nic tam zapisać. W momencie, gdy używasz zapisu '@id' Path musi mieć wartość, bo w końcu próbujesz przypisać temu elementowi jakiś atrybut. Gdy przeglądałem dokumentację msdn na temat tych zapytań to okazuje się, że ich składnia jest bardzo elastyczna. Niezbędna jest jednak znajomość podstaw XPath, aby móc w pełni je wykorzystać.

Jak to mówią "im dalej w las, tym więcej drzew". Ja też dziś dopiero po raz pierwszy miałem styczność z tymi zapytaniami :slight_smile:


(Grzegorz Pyzera) #5

No dobra, nawet wow poradziłem sobie z resztą.. a teraz pytanie już takie bardziej ogólne: co trzeba zrobić, żeby wynik tego zapytania po wywołaniu automatycznie zapisał się do pliku? :roll:


(Tomek Matz) #6

Nie wiem, czy masz już rozwiązanie ... Anyway narzędzie, które umożliwia wykonanie zapisu wyniku zapytania do pliku XML to bcp (odpal w wierszu poleceń Windows komendę bcp /?, aby zobaczyć help tego programu). Na rozwiązanie naprowadził mnie ten link http://msdn.microsoft.com/en-us/library/ms191184.aspx.

W googlach trafiłem na taki link http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/51c7ddba-04c7-41da-84e3-95b5af8433b7. Poprawiłem kod, który umieściła tamta osoba i powstało coś takiego:

DECLARE 

	@strXML varchar(MAX),

	@filePath varchar(100), 

	@bcpCommand varchar(5000);


	SET @strXML = (

		SELECT (

			SELECT tabela.Id as 'Rozmiar/@id', tabela.Wartosc as 'Rozmiar'

			FROM tabela

			WHERE tabela.PkId IN (1, 2, 6, 9, 13)

			FOR XML PATH(''), type

		) AS Rozmiary

		FOR XML PATH('Root'));

	SET @strXML = 'SELECT ''<?xml version="1.0" encoding="UTF-16"?>' + @strXML + '''';

	SET @strXML = (SELECT REPLACE(@strXML, '"', '""'));

	--PRINT @strXML;


	SET @filePath = 'C:\Users\matzu\Desktop\test.xml';

	SET @bcpCommand = 'bcp "' + @strXML + '" queryout "' + @filePath + '" -T -c -x';

	--PRINT @bcpCommand;


	EXEC master..xp_cmdshell @bcpCommand

Ten kod możesz sobie umieścić np. w jakiejś procedurze. Spowoduje on uruchomienie narzędzia bcp, które wykona omawiane przez nas wcześniej zapytanie SQL (trochę zmodyfikowałem jego kod), a jego wynik zapisze do pliku XML. Jeśli nie chciałbyś użyć procedury to możesz np. przygotować sobie plik .bat, który posłuży do odpalania tego programu bcp z odpowiednimi parametrami (z których jeden to zapytanie SQL).


(Grzegorz Pyzera) #7

przerobiłem i mam błąd.

Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.

(Tomek Matz) #8

U mnie ten błąd na SQL Server 2008 nie wyskakuje (kod działa). No ale wprowadziłem poprawki na podstawie komunikatu błędu, który podałeś.

DECLARE 

	@strXML varchar(MAX),

	@filePath varchar(100), 

	@bcpCommand varchar(5000);


	SET @strXML = CONVERT(varchar(MAX), (

			SELECT (

				SELECT tabela.Id as 'Rozmiar/@id', tabela.Wartosc as 'Rozmiar'

				FROM tabela

				WHERE tabela.PkId IN (1, 2, 6, 9, 13)

				FOR XML PATH(''), type

			) AS Rozmiary

			FOR XML PATH('Root')

		));

	SET @strXML = 'SELECT ''<?xml version="1.0" encoding="UTF-16"?>' + @strXML + '''';

	SET @strXML = (SELECT REPLACE(@strXML, '"', '""'));

	--PRINT @strXML;


	SET @filePath = 'C:\Users\matzu\Desktop\test.xml';

	SET @bcpCommand = 'bcp "' + @strXML + '" queryout "' + @filePath + '" -T -c -x';

	--PRINT @bcpCommand;


	EXEC master..xp_cmdshell @bcpCommand

Zobacz, czy teraz ruszy. Oczywiście musisz podmienić ścieżkę w zmiennej @filePath.