wtorek, 11 listopada 2008

Transformacja wyniku zapytania do innego formatu

Dzisiaj zajmę się tematem formatowania wyniku zapytania. Czasami zachodzi potrzeba aby wynik zapytania był w jakimś formacie, który łatwiej umożliwia wizualizację danych. Tym formatem może być HTML, XHTML. W poście tym przedstawię dwie techniki formatowania / transformowania wyniku zapytania.

HTML w T-SQL

Pierwszą z technik, którą przedstawię zaprezentował na swoim blogu Tony Rogerson. Bardzo ciekawa technika umożliwiająca otrzymanie wyniku zapytania jako dokumentu HTML. Poniższe zapytanie korzysta z bazy danych Northwind.

declare @body varchar(max)

set @body = cast( (
select 'company_name' as "
td/@class",td = CompanyName + '</td><td>' + ContactName + '</td><td>' + ContactTitle + '</td><td>' + Address +'</td><td>' + City + '</td><td>' + PostalCode + '</td><td>' + Country
from (
select CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country


from customers
) as d
for xml path( 'tr' ), type ) as varchar(max) )


set @body = '<html><head><style>'
+ 'TABLE {border: 1px; width:600px}'
+ 'TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space:nowrap}'
+ 'TD {font-size: 12px; font-family:verdana; white-space: nowrap}'
+ '.table_header {font-family: Verdana; font-size: 14px;}'
+ '.company_name {color:black; font-weight:bold;}'
+ '</style></head><body>'
+ '<h2>Northwind Customers</h2><table cellspacing="0">'
+'<trclass="table_header"><th>CompanyName</th><th>ContactName</th><th>Title</th><th>Address</th>' +'<th>City</th><th>PostalCode</th><th>Country</th></tr>' + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' ) + '</table></body></html>'

Powyższe zapytanie tworzy łańcuch znaków, będący dokumentem HTML. Jak widać poza znacznikami, można też utworzyć style CSS, które formatują dokument.

Aby zobaczyć dokument wynikowy wystarczy wykonać poniższe zapytanie.

select CONVERT(xml, @body)

Technika ta wykorzystuje tylko i wyłącznie T-SQL. Wszystkie znaczniki oraz style są umieszczone w łańcuchach znaków. Nie ma potrzeby odwołań do zewnętrznych komponentów. Minusem tej techniki jest to, że w przypadku bardziej skomplikowanego dokumentu można bardzo łatwo zapomnieć o domknięciu jakiego znacznika. Poza tym czytelność zapytania także ulega degradacji wraz ze wzrostem jego objętości.

HTML z użyciem CLR, XML, XSLT

Ten sam rezultat można otrzymać korzystając z innej techniki, a mianowicie zwrócić wynik zapytania jako dokument XML i wykonać na nim transformaty za pomocą XSLT. Jako, że SQL Server nie umożliwia wykonania tego typu operacji należy skorzystać z możliwości jakie niesie ze sobą CLR.

W celu wykonania transformacji utworzę funkcję, która jako parametry będzie przyjmowała dokument XML, dokument XSLT. Funkcja ta zwracać będzie dokument w innym formacie. Celowo użyłem sformułowania „w innym formacie”, gdyż wartością zwróconą przez tą funkcję nie musi być dokument HTML. Kilka słów więcej na ten temat będzie na zakończenie artykułu.

Tak więc korzystając z Visual Studio, tworzę nowy projekt, którym jest nowa klasa.

Poniżej cały kod klasy, napisanej w VB.NET, zawierającej funkcję, która transformuje wynik zapytania.
Imports System
Imports System.Data.SqlTypes
Imports System.IO

Imports System.Xml
Imports System.Xml.xsl
Imports Microsoft.SqlServer.Server

Partial Public Class CLRToolsSuite
<SqlFunction(isdeterministic:=True, DataAccess:=DataAccessKind.None)> _

Public Shared Function XSLTransform(ByVal xmlDoc As SqlXml, ByVal xslDoc As SqlXml) As SqlXml

Dim memory_stream As New MemoryStream()
Dim lxmlDoc As New XmlDocument
Dim lxslTransform As New XslCompiledTransform
Dim lxmlWriter As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
Dim xmlReader As XmlTextReader

lxmlDoc.InnerXml = xmlDoc.Value
lxslTransform.Load(xslDoc.CreateReader)
lxslTransform.Transform(lxmlDoc, lxmlWriter)
memory_stream.Seek(0, SeekOrigin.Begin)
xmlReader = New XmlTextReader(memory_stream)

Return New SqlXml(xmlReader)

End Function
End Class

Teraz potrzeba tylko skompilować projekt. Mając utworzoną bibliotekę .dll możemy ją zarejestrować w SQL Server.

Na początek jednak trzeba włączyć możliwość korzystania z CLR. Domyślnie ta usługa, zresztą tak samo jak wiele innych rzeczy, w SQL Server jest wyłączona.

exec sp_configure 'clr enabled', 1
RECONFIGURE
GO

Mając włączony CLR można już zarejestrować naszą bibliotekę a także zadeklarować nową funkcję.

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_XSLTransform(@xmldoc as xml, @xsldoc as xml)
RETURNS XML
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].XSLTransform;
GO

Ostatnim elementem, no poza samym zapytaniem, tej układanki jest dokument zawierający style potrzebne do przekształcenia wyniku zapytania.

Nie chcę w tym miejscu wgłębiać się w XSLT. Temat ten mógłby zająć prawdopodobnie kilkanaście artykułów. Poniżej kod dokumentu XSLT, który przekształci wynik zapytania do dokumentu HTML, takiego samego jak zaprezentowany został w poprzedniej technice.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">


<xsl:template match="/">


<html>
<head>
<style>
TABLE {border: 1px; width:600px; padding: 2px;}
TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space: nowrap}
TD {font-size: 12px; font-family:verdana; white-space: nowrap}
.table_header {font-family: Verdana; font-size: 14px;}
.company_name {color:black; font-weight:bold;}
</style>
</head>


<body>
<h2>Northwind Customers</h2>
<table cellspacing="0">
<tr class="table_header">
<th>Company Name</th>
<th>Contact Name</th>
<th>Title</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th></tr>


<xsl:for-each select="xmldoc/row">
<tr>
<td class="company_name">
<xsl:value-of select="CompanyName"/>
</td>
<td>
<xsl:value-of select="ContactName"/>
</td>
<td>
<xsl:value-of select="ContactTitle"/>
</td>
<td>
<xsl:value-of select="Address"/>
</td>
<td>
<xsl:value-of select="City"/>
</td>
<td>
<xsl:value-of select="PostalCode"/>
</td>
<td>
<xsl:value-of select="Country"/>
</td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Tak oto stworzony dokument zapiszę do pliku .xslt. Równie dobrze można by go zapisać gdzieś w jakiejś tabeli w bazie danych, jednakże na potrzeby tego artykułu skorzystam z pliku, który później będzie załadowany do zmiennej.

I teraz już pora przejść do SQL-a i wykonać zapytanie.

DECLARE @XMLDOC AS XML
SELECT @XMLDOC = (SELECT CompanyName, ContactName, ContactTitle, Address, City, PostalCode, CountryFROM Customers FOR XML PATH('row'), ROOT('xmldoc'))


DECLARE @xsldoc AS XMLselect @xsldoc = xd from openrowset(BULK 'c:\xsl.xsl', SINGLE_BLOB) as import(xd)


I ponownie, aby zobaczyć rezultat wystarczy wykonać jeszcze jedno zapytanie.

select dbo.fn_XSLTransform(@xmldoc,@xsldoc)

Podsumowanie

Zarówno pierwsza jak i druga zaprezentowana technika pozwala nam uzyskać zapytanie sformatowane w postaci kodu HTML. O ile w pierwszej technice wykorzystany był tylko T-SQL o tyle druga wykorzystuje CLR i XSLT. Tak jak już wspomniałem w przypadku wykorzystania techniki tylko z T-SQL nie trudno jest o dopuszczenie się błędu w trakcie pisania znaczników HTML. W przypadku drugiej techniki, pomimo tego, że dokument XSLT ma znacznie większą objętość, to korzystając z edytora, takiego jak np. Visual Studio, znacznie trudniej o popełnienie błędu w samym dokumencie.

Inną bardzo dużą zaletą XSLT jest to, że korzystając ze stylów można utworzyć praktycznie dowolny dokument. Sam XSLT jest bardzo potężnym narzędziem, umożliwiającym skomplikowane operacji transformacji danych.


Prześlij komentarz