DECLARE @book TABLE
(
BookID int,
Details xml
);
INSERT INTO @book
VALUES (1,
'<book id="bk101" xmlns="http://rmlalves.blogspot.com/">
<author>Gambardella, Matthew</author>
<title>XML Developer''s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications with XML.</description>
</book>');
INSERT INTO @book
VALUES (2,
'<book id="bk102" xmlns="http://rmlalves.blogspot.com/">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description>
</book>');
INSERT INTO @book
VALUES (3,
'<book id="bk103" xmlns="http://rmlalves.blogspot.com/">
<author>Corets, Eva</author>
<title>Maeve Ascendant</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-11-17</publish_date>
<description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>
</book>');
INSERT INTO @book
VALUES (4,
'<book id="bk104" xmlns="http://rmlalves.blogspot.com/">
<author>Corets, Eva</author>
<title>Oberon''s Legacy</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-03-10</publish_date>
<description>In post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant.</description>
</book>');
INSERT INTO @book
VALUES (5,
'<book id="bk105" xmlns="http://rmlalves.blogspot.com/">
<author>Corets, Eva</author>
<title>The Sundered Grail</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-09-10</publish_date>
<description>The two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon''s Legacy.</description>
</book>');
INSERT INTO @book
VALUES (6,
'<book id="bk106" xmlns="http://rmlalves.blogspot.com/">
<author>Randall, Cynthia</author>
<title>Lover Birds</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-09-02</publish_date>
<description>When Carla meets Paul at an ornithology conference, tempers fly as feathers get ruffled.</description>
</book>');
INSERT INTO @book
VALUES (7,
'<book id="bk107" xmlns="http://rmlalves.blogspot.com/">
<author>Thurman, Paula</author>
<title>Splish Splash</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-11-02</publish_date>
<description>A deep sea diver finds true love twenty thousand leagues beneath the sea.</description>
</book>');
INSERT INTO @book
VALUES (8,
'<book id="bk108" xmlns="http://rmlalves.blogspot.com/">
<author>Knorr, Stefan</author>
<title>Creepy Crawlies</title>
<genre>Horror</genre>
<price>4.95</price>
<publish_date>2000-12-06</publish_date>
<description>An anthology of horror stories about roaches, centipedes, scorpions and other insects.</description>
</book>');
INSERT INTO @book
VALUES (9,
'<book id="bk109" xmlns="http://rmlalves.blogspot.com/">
<author>Kress, Peter</author>
<title>Paradox Lost</title>
<genre>Science Fiction</genre>
<price>6.95</price>
<publish_date>2000-11-02</publish_date>
<description>After an inadvertant trip through a Heisenberg Uncertainty Device, James Salway discovers the problems of being quantum.</description>
</book>');
INSERT INTO @book
VALUES (10,
'<book id="bk110" xmlns="http://rmlalves.blogspot.com/">
<author>O''Brien, Tim</author>
<title>Microsoft .NET: The Programming Bible</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-09</publish_date>
<description>Microsoft''s .NET initiative is explored in detail in this deep programmer''s reference.</description>
</book>');
INSERT INTO @book
VALUES (11,
'<book id="bk111" xmlns="http://rmlalves.blogspot.com/">
<author>O''Brien, Tim</author>
<title>MSXML3: A Comprehensive Guide</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-01</publish_date>
<description>The Microsoft MSXML3 parser is covered in detail, with attention to XML DOM interfaces, XSLT processing, SAX and more.</description>
</book>');
INSERT INTO @book
VALUES (12,
'<book id="bk112" xmlns="http://rmlalves.blogspot.com/">
<author>Galos, Mike</author>
<title>Visual Studio 7: A Comprehensive Guide</title>
<genre>Computer</genre>
<price>49.95</price>
<publish_date>2001-04-16</publish_date>
<description>Microsoft Visual Studio 7 is explored in depth, looking at how Visual Basic, Visual C++, C#, and ASP+ are integrated into a comprehensive development environment.</description>
</book>');
Tendo esta tabela agora queremos fazer queries sobre o xml, para isso o sql tem um conjunto de métodos de ajuda para podermos aplicar uma expressão xpath à coluna xml.
Em conjunto com esses métodos também temos um helper para indicar à query os xml namespaces a usar.
WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)
SELECT
Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,
Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,
Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,
Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price
FROM @book
WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)
SELECT
Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,
Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,
Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,
Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price
FROM @book
WHERE Details.value('(/cnt:book/cnt:price)[1]', 'money') > 40
Fonte: Add Namespaces to Queries with WITH XMLNAMESPACES
Já agora no próximo post fala dos problemas de ter campos de xml, da falta de desempenho.
ReplyDelete