SQL export do Wordu, 1. část: FOR XML

Nedávno jsem dostal za úkol doplnit do jedné databázové aplikace on-demand report do Wordu. Po chvilce přemýšlení jsem se rozhodl pro řešení, které bych rád rozkryl v několika následujících článcích. Za prvé se dle mého názoru jedná o celkem elegantní skloubení několika doménových znalostí a za druhé jde o vcelku univerzální přístup, vhodný zejména pro situace, kdy nemá cenu nasazovat full-featured SQL Server Reporting Services.

Chtěl bych vás v tomto seriálu provést kompletním řešením reportu na testovacích datech Adventue Works 2014 OLTP1, ze kterých budu chtít získat seznam zákazníků z daného teritoria a všechny jejich objednávky i s detailem zakoupených položek.

První krok je vykutat informace z databáze. SQL Server2 už od verze 2005 obsahuje vcelku šikovné příkazy na formátování výsledku selectu jako XML. (Ti, kteří mě znají, to nepřekvapí a ostatní čtenáři si doufám postupně zvyknou, že se všechny problémy snažím převést na XML 🙂 )

Na seznámení s touto funkcionalitou můžete zkusit přidat na konec v podstatě libovolného selectu FOR XML AUTO, ELEMENTS, ROOT('data') a sledujte, co se stane. Je velká šance, že se to bude blížit kýženému tvaru XML výstupu. Teoreticky bych si s tím vystačili i zde, ale ušetřím si práci později, když použiji mód FOR XML PATH, místo joinů použiju subselecty a sloupce opatřím xpathovými aliasy. Jejich vhodnou kombinací lze naformátovat XML do libovolné podoby. Mojí ambicí však bylo FOR XML jen představit, ne popisovat syntax a přesné chování, takže podrobnosti nechám na individuální nastudování z MSDN (FOR XML).

V dalším díle se podíváme jak s takovým XML můžeme pracovat.

SQL dotaz:

SELECT
	p.FirstName AS [@name],
	p.LastName AS [@surname],
	c.AccountNumber AS [@accountNumber],
	p.Demographics AS [*],
	(
		SELECT
			h.SalesOrderNumber AS [@orderNumber],
			h.OrderDate AS [@date],
			h.SubTotal AS [@itemPrice],
			h.TaxAmt AS [@tax],
			h.freight AS [@shipping],
			h.TotalDue AS [@totalPrice],
			(
				SELECT
					p.Name AS [@name],
					p.Color AS [@color],
					d.OrderQty AS [@quantity],
					d.LineTotal AS [@totalPrice]
				FROM Sales.SalesOrderDetail d
				JOIN Production.Product p on d.ProductID=p.ProductID
				WHERE h.SalesOrderID=d.SalesOrderID
				FOR XML PATH('Item'), TYPE
			)
		FROM Sales.SalesOrderHeader h
		WHERE c.CustomerID=h.CustomerID
		FOR XML PATH('Order'), TYPE
	)
FROM Sales.Customer c
JOIN Person.Person p ON c.PersonID=p.BusinessEntityID
WHERE c.TerritoryID=@TerritoryID
FOR XML PATH('Customer'), ROOT('Customers')

Generované XML:

<Customers>
	<Customer name="Ebony" surname="Gill" accountNumber="AW00011533">
		<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
			<TotalPurchaseYTD>-35</TotalPurchaseYTD>
			<DateFirstPurchase>2003-11-15Z</DateFirstPurchase>
			<BirthDate>1957-07-21Z</BirthDate>
			<MaritalStatus>M</MaritalStatus>
			...
		</IndividualSurvey>
		<Order orderNumber="SO57966" date="2013-10-14T00:00:00" itemPrice="2.2900" tax="0.1832" shipping="0.0573" totalPrice="2.5305">
			<Item name="Patch Kit/8 Patches" quantity="1" totalPrice="2.290000" />
		</Order>
		<Order orderNumber="SO74548" date="2014-06-12T00:00:00" itemPrice="35.0000" tax="2.8000" shipping="0.8750" totalPrice="38.6750">
			<Item name="HL Mountain Tire" quantity="1" totalPrice="35.000000" />
		</Order>
	</Customer>
	<Customer name="Jay" surname="Romero" accountNumber="AW00013099">
		<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
			...
		</IndividualSurvey>
		<Order orderNumber="SO57382" date="2013-10-03T00:00:00" itemPrice="120.0000" tax="9.6000" shipping="3.0000" totalPrice="132.6000">
			<Item name="Hitch Rack - 4-Bike" quantity="1" totalPrice="120.000000" />
		</Order>
		<Order orderNumber="SO68375" date="2014-03-16T00:00:00" itemPrice="96.9600" tax="7.7568" shipping="2.4240" totalPrice="107.1408">
			<Item name="HL Mountain Tire" quantity="1" totalPrice="35.000000" />
			<Item name="Mountain Tire Tube" quantity="1" totalPrice="4.990000" />
			<Item name="Fender Set - Mountain" quantity="1" totalPrice="21.980000" />
			<Item name="Sport-100 Helmet, Red" color="Red" quantity="1" totalPrice="34.990000" />
		</Order>
	</Customer>
	...
</Customers>

1 Kdo by měl problémy s instalací, mohlo by pomoct Readme for Adventure Works 2014 Sample Databases.

2 Píši sice o SQL Serveru, ale ostatní databáze také nabízejí nějakou formu XML exportu. PostgreSQL například zavádí funkce XMLFOREST, XMLELEMENT, XMLATTRIBUTE, atp. jejichž skládáním lze XML tvarovat. Srozumitelně popsáno na wiki PostgreSQL (XML Support). XML výstup, ovšem bez možnosti vlastního formátování, nabízí i MySQL a to za použití --xml parametru. Více informací v dokumentaci MySQL (Options, –xml).

1 komentář: „SQL export do Wordu, 1. část: FOR XML

  1. Pingback: SQL export do Wordu, 2. část: XSLT | Hello world

Zanechat odpověď

Vyplňte detaily níže nebo klikněte na ikonu pro přihlášení:

Logo WordPress.com

Komentujete pomocí vašeho WordPress.com účtu. Odhlásit /  Změnit )

Google photo

Komentujete pomocí vašeho Google účtu. Odhlásit /  Změnit )

Twitter picture

Komentujete pomocí vašeho Twitter účtu. Odhlásit /  Změnit )

Facebook photo

Komentujete pomocí vašeho Facebook účtu. Odhlásit /  Změnit )

Připojování k %s