{"id":278,"date":"2007-06-27T00:00:00","date_gmt":"2007-06-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/xml-jumpstart-workbench\/"},"modified":"2024-08-12T10:14:33","modified_gmt":"2024-08-12T10:14:33","slug":"xml-jumpstart-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/xml-jumpstart-workbench\/","title":{"rendered":"XML Jumpstart Workbench"},"content":{"rendered":"<p>Some of the frustration of learning XML is in not being able to see clearly an entire process. So many of the existing manuals concentrate on just one step in a chain and leave the reader saying &#8216;very technological. So what?&#8217;<\/p>\n<p>In other cases, XML is used in illustrations with tiny fragments of real data, losing sight of the great value of XML as a means of exchanging large amounts of information.<\/p>\n<p>We&#8217;ll try to take a different approach here in order to give it all some purpose. We&#8217;re not going to even try to provide anything comprehensive here, more of a quick spin around the block.<\/p>\n<p>OK. Let&#8217;s assume that we want an application which seeks to list out the nearest hundred pubs to any postcode in Britain. (apologies to our many friends outside Britain).<\/p>\n<p>We have two XML files that gives the raw data, collected from several publicly available sources. they are supplied with the article. One gives the location of every &#8216;outcode&#8217;, and the other every pub, with their postcode. Tantalising?<\/p>\n<p>In this Workbench we will:<\/p>\n<ul>\n<li><a href=\"#First\">Read an XML file into a SQL Server XML Variable<\/a><\/li>\n<li><a href=\"#Second\">Shred a document fragment in an XML variable into a SQL Server table<\/a><\/li>\n<li><a href=\"#Third\">Query a the contents of an XML variable for a set of values as an XML result<\/a><\/li>\n<li><a href=\"#Fourth\">Assign the result of a SQL query to an XML variable<\/a><\/li>\n<li><a href=\"#Fifth\">Store the contents of an XML variable to a table<\/a><\/li>\n<li><a href=\"#Sixth\">Save an XML document fragment to a file.<\/a><\/li>\n<\/ul>\n<p>Let&#8217;s read the XML location information from a file into a table!<\/p>\n<h2 id=\"First\">Reading an XML file into a SQL Server XML Variable<\/h2>\n<p>Assume we have the XML file unzipped in <code>C:\\workbench\\locations.xml<\/code>. The files can be downloaded at the bottom of the article. (&#8216;UK Locations XML Xipped&#8217; and &#8216;UK Pubs XML Zipped&#8217;) The data is over a Mb in size, so be warned! You&#8217;ll need to unzip them too!<\/p>\n<p>First pull the file into a conventional relational table&#8230;<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE @xmlLocations XML \nSELECT  @xmlLocations = BulkColumn \nFROM    OPENROWSET(BULK 'C:\\workbench\\locations.xml', SINGLE_BLOB) AS x  \n\/* we can store this XML data in  a table with an XML column *\/ \nCREATE TABLE xTable \n    ( \n      xTable_ID INT IDENTITY \n                    PRIMARY KEY, \n      xCol XML \n    ) ; \n\/* we then insert a row into the table from the XML variable *\/ \nINSERT  INTO xTable ( xCol ) \n        SELECT  @xmlLocations \n<\/pre>\n<h2 id=\"Second\">Shredding a document fragment in an XML variable into a SQL Server table<\/h2>\n<p>Now we&#8217;ll put it into a conventional table for working on. First we define it.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE TABLE [dbo].[location] \n    ( \n      [location_ID] [int] IDENTITY(1, 1) \n                          NOT NULL, \n      [whereabouts] [varchar](100) NULL, \n      [Town] [varchar](80) NULL, \n      [city] [varchar](80) NULL, \n      [county] [varchar](40) NULL, \n      [region] [varchar](40) NULL, \n      [outcode] [varchar](4) NULL, \n      [x] [int] NULL, \n      [y] [int] NULL, \n      [latitude] [numeric](18, 3) NULL, \n      [longitude] [numeric](18, 3) NULL \n    ) \nON  [PRIMARY] \n<\/pre>\n<p>And now we can simply shred the XML data type that we&#8217;ve read in into the table, using the XML Data type <code>nodes()<\/code> method.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">INSERT  INTO location \n        ( \n          whereabouts, \n          town, \n          city, \n          county, \n          region, \n          outcode, \n          x, \n          y, \n          latitude, \n          Longitude \n        ) \n        SELECT  x.location.value( \n                    'whereabouts[1]', 'varchar(100)') AS whereabouts, \n                x.location.value( \n                    'town[1]', 'varchar(80)') AS town, \n                x.location.value( \n                    'city[1]', 'varchar(80)') AS city, \n                x.location.value( \n                    'county[1]', 'varchar(40)') AS county, \n                x.location.value( \n                    'region[1]', 'varchar(40)') AS region, \n                x.location.value( \n                    'outcode[1]', 'varchar(4)') AS outcode, \n                x.location.value( \n                    'x[1]', 'int') AS x, \n                x.location.value( \n                    'y[1]', 'int') AS y, \n                x.location.value( \n                    'latitude[1]', 'numeric(18, 3) ') AS latitude, \n                 x.location.value( \n                    'longitude[1]', 'numeric(18, 3)') AS longitude \n        FROM    @xmlLocations.nodes('\/\/locations\/location')  \n                                    AS x ( location ) \n\n\/* Now we can try it out by finding the nearest places to a particular postcode*\/ \n\nGO \nCREATE PROCEDURE spWhereIsThis @Postcode VARCHAR(10) \n\/* \nspWhereIsThis 'cm2' \n*\/ \nAS  \n    DECLARE @x INT, \n        @y INT \n\n    SELECT TOP 1--find out our coordinates \n            @x = x, \n            @y = y \n    FROM    location \n    WHERE   outcode LIKE RTRIM(LEFT(SUBSTRING(@Postcode, 1, \n                              CHARINDEX(' ', @Postcode + ' ', \n                                                 1) - 1), 4)) \n    IF @@Rowcount = 0 --typo! \n        BEGIN \n            RAISERROR ( 'I don''t recognise the postcode ''%s''', 16, 1, \n                @postcode ) \n            RETURN 1 \n        END \n    SELECT TOP 100--list the 100 nearest locations \n            whereabouts, \n            region, \n            [miles] = ROUND(SQRT(SQUARE(X - @X) + SQUARE(Y - @Y))  \n                       * 0.0006214,0) \n    FROM    location \n    WHERE   x IS NOT NULL \n    ORDER BY miles \nGO \n<\/pre>\n<h2 id=\"Third\">Querying the contents of an XML variable for a set of values as an XML result<\/h2>\n<p>We can extract data directly from the XML column if we wish:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT  xCol.query(' \n   for $LOC in \/locations\/location \n   where $LOC\/outcode[.=\"CM2\"] \n   return \n     &lt;coordinate&gt; \n      { $LOC\/x } \n      { $LOC\/y } \n      { $LOC\/town } \n     &lt;\/coordinate&gt; \n') AS Result \nFROM    [XTable] \n\n\/* but as the XQUERY string must be a string literal and not a  \nstring variable, we need to put parameters in via a sql:variable \nparameter like this....*\/ \n\nDECLARE @Postcode VARCHAR(10) \nSELECT  @Postcode = 'CO8' \nSELECT  xCol.query(' \n   for $LOC in \/locations\/location \nwhere $LOC\/outcode = sql:variable(\"@Postcode\")  \n   return \n     &lt;locations&gt; \n      { $LOC\/whereabouts } \n      { $LOC\/county } \n     &lt;\/locations&gt; \n') AS Result \nFROM    [XTable] \n<\/pre>\n<p>Now, this is an open-ended workbench. You have a nice large source of data, and BOL is now screaming at you to be read. Try out some FLWOR!<\/p>\n<p>When you tire, it is time to pull in a list of British pubs.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE @xmlPubs XML \nSELECT  @xmlPubs = BulkColumn \nFROM    OPENROWSET(BULK 'C:\\workbench\\pubs.xml', SINGLE_BLOB) AS x  \nCREATE TABLE dbo.Pub \n    ( \n      Pub_ID INT IDENTITY(1, 1) \n                 NOT NULL, \n      [Name] VARCHAR(30) NOT NULL, \n      Address VARCHAR(100) NOT NULL, \n      outcode VARCHAR(4) NOT NULL, \n      x INT NULL, \n      y INT NULL \n    ) \nON  [PRIMARY] \nINSERT  INTO Pub \n        ( \n          [name], \n          address, \n          outcode \n        ) \n        SELECT  x.pub.value('name[1]', 'varchar(30)'), \n                x.pub.value('address[1]', 'varchar(100)'), \n                RTRIM(LEFT( \n                     SUBSTRING(x.pub.value('postcode[1]', 'varchar(10)'), \n                                     1, \n                                     CHARINDEX(' ', \n                                               x.pub.value('postcode[1]', \n                                                           'varchar(10)') \n                                               + ' ', 1) - 1), 4 \n                    )) \n        FROM    @xmlPubs.nodes('\/\/pubs\/pub') AS x ( pub ) \nUPDATE  pub \nSET     x = f.x, y = f.y \nFROM    pub \n        INNER JOIN location f ON pub.outcode = f.outcode \n\nCREATE PROCEDURE spNearestPubs @Postcode VARCHAR(10) \n\/* \nspNearestPubs 'co10' \n*\/ \nAS  \n    DECLARE @x INT, \n        @y INT \n\n    SELECT TOP 1--find out our coordinates \n            @x = x, \n            @y = y \n    FROM    location \n    WHERE   outcode LIKE RTRIM(LEFT(SUBSTRING(@Postcode, 1, \n                                         CHARINDEX(' ', @Postcode + ' ', \n                                                            1) - 1), 4)) \n    IF @@Rowcount = 0 --typo! \n        BEGIN \n            RAISERROR ( 'I don''t recognise the postcode ''%s''', 16, 1, \n                @postcode ) \n            RETURN 1 \n        END \n    SELECT TOP 100--list the 100 nearest locations \n            name + ' ' + address, \n            [miles] = ROUND( \n                           SQRT(SQUARE(X - @X) + SQUARE(Y - @Y)) \n                           * 0.0006214,0) \n    FROM    pub \n    WHERE   x IS NOT NULL \n    ORDER BY miles \nGO \n<\/pre>\n<h2 id=\"Fourth\">Assigning the result of a SQL query to an XML variable<\/h2>\n<p>Well, nice as far as it goes, but why not pass the result back as an XML variable, and we&#8217;ll then we can save it direct to disk, or send it happily to an application, store it in a table as a variable? XML could be quite handy!<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE PROCEDURE spNearestPubsXML \n    @Postcode VARCHAR(10), \n    @XMLPubList XML OUTPUT \n\/* \ne.g. \nDeclare @PubList xml \nexecute spNearestPubsXML 'co10',   @XMLPubList=@PubList output \nSelect @PubList \n*\/ \nAS  \n    DECLARE @x INT, \n        @y INT \n\n    SELECT TOP 1--find out our coordinates \n            @x = x, \n            @y = y \n    FROM    location \n    WHERE   outcode LIKE RTRIM(LEFT(SUBSTRING(@Postcode, 1, \n                                        CHARINDEX(' ', @Postcode + ' ', \n                                                           1) - 1), 4)) \n    IF @@Rowcount = 0 --typo! \n        BEGIN \n            RAISERROR ( 'I don''t recognise the postcode ''%s''', 16, 1, \n                @postcode ) \n            SET @XMLPubList = '&lt;pubs \/&gt;' \n            RETURN 1 \n        END \n    SET @XMLPubList = ( SELECT TOP 100--list the 100 nearest locations \n                                [name] = name + ', ' + address, \n                                [miles] = CONVERT(INT,  \n                                            ROUND(SQRT( \n                                               SQUARE(X - @X)  \n                                               + SQUARE(Y - @Y)) \n                                            * 0.0006214, 0)) \n                        FROM    pub \n                        WHERE   x IS NOT NULL \n                        ORDER BY miles \n                      FOR \n                        XML PATH('pub'), \n                            ROOT('pubs'), \n                            TYPE \n                      ) \nGO \n--we can then save the results to disk very easily \nDECLARE @PubList XML \nEXECUTE spNearestPubsXML 'BR2', @XMLPubList = @PubList OUTPUT \n<\/pre>\n<h2 id=\"Fifth\">We store the contents of an XML variable to a table<\/h2>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE TABLE xNearestPubs \n    ( \n      xPubs_ID INT IDENTITY \n                   PRIMARY KEY, \n      xCol XML \n    ) ; \nINSERT  INTO xNearestPubs ( xCol ) \n        SELECT  @PubList \n<\/pre>\n<h2 id=\"Sixth\">We Save an XML value to a file.<\/h2>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE @Command VARCHAR(255) \nDECLARE @Filename VARCHAR(100) \nSELECT  @Filename = 'C:\\workbench\\Nearestpubs.xml' \n\/* we then insert a row into the table from the XML variable *\/ \n\/* so we can then write it out via BCP! *\/ \nSELECT  @Command = 'bcp \"select xCol from ' + DB_NAME() \n        + '..xNearestPubs\" queryout '  \n       + @Filename + ' -w -T -S' + @@servername \nEXECUTE master..xp_cmdshell @command \n--so now the xml is written out to a file \n<\/pre>\n<p>So there we have it. Hopefully, if you enjoyed this approach to XML by example, we&#8217;ll try out more complex examples in further Workbenches.<\/p>\n<p>In the meantime, there are other resources on the Simple-Talk site:<\/p>\n<p><strong><a href=\"http:\/\/www.simple-talk.com\/sql\/sql-server-2005\/beginning-sql-server-2005-xml-programming\/\">Beginning SQL Server 2005 XML Programming<\/a><\/strong>, Srinivas Sampath 21 February 2006<br \/>\nXML has been used to represent semi-structured (as well as unstructured) data such as documents and emails. If information in these models has to be queried, then XML is probably the simplest way to represent such information. <a id=\"ctl00_MainContent_repArticles_ctl00_lnkMore\" href=\"http:\/\/www.simple-talk.com\/sql\/sql-server-2005\/beginning-sql-server-2005-xml-programming\/\">Read more&#8230;<\/a><\/p>\n<p><strong><a href=\"http:\/\/www.simple-talk.com\/dotnet\/performance\/understanding-xml-web-services-for-testers\/\">Understanding XML web services for testers<\/a><\/strong>, Helen Joyce &#8211; 13 April 2003<br \/>\nThis White Paper investigates how XML web services are implemented, considers the customizable features of web services and looks at load testing a web service, specifically so that test strategies can be formulated. <a id=\"ctl00_MainContent_repArticles_ctl02_lnkMore\" href=\"http:\/\/www.simple-talk.com\/dotnet\/performance\/understanding-xml-web-services-for-testers\/\">Read more&#8230;<\/a><\/p>\n<p><strong><a href=\"http:\/\/www.simple-talk.com\/opinion\/opinion-pieces\/xml-and-rdbms-10-years-on\/\">XML and RDBMS: 10 years on<\/a><\/strong>, Jim Fuller &#8211; 25 August 2006<br \/>\nAs we approach the 10-year anniversary of XML Jim Fuller provides a personal retrospective, focussing on how XML has been and will be used with the RDBMS. <a id=\"ctl00_MainContent_repArticles_ctl04_lnkMore\" href=\"http:\/\/www.simple-talk.com\/opinion\/opinion-pieces\/xml-and-rdbms-10-years-on\/\">Read more&#8230;<\/a><\/p>\n<p><strong><a href=\"http:\/\/www.simple-talk.com\/sql\/learn-sql-server\/sql-server-xml-cribsheet\/\">SQL Server XML Cribsheet<\/a><\/strong>, Robyn Page &#8211; 29 March 2007<br \/>\nIf you find XML a bit of an acronym minefield, Robyn&#8217;s Cribsheet will help sort out your XSLT from your XDM. <a id=\"ctl00_MainContent_repArticles_ctl06_lnkMore\" href=\"http:\/\/www.simple-talk.com\/sql\/learn-sql-server\/sql-server-xml-cribsheet\/\">Read more&#8230;<\/a><\/p>\n<p>And we would also recommend you to read Jacob Sebastian&#8217;s excellent articles on SQL Server Central:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.sqlservercentral.com\/columnists\/jSebastian\/2982.asp\">Advanced XML Processing<\/a><\/li>\n<li><a href=\"http:\/\/www.sqlservercentral.com\/columnists\/jSebastian\/2977.asp\">Passing a Table to A Stored Procedure<\/a><\/li>\n<li><a href=\"http:\/\/www.sqlservercentral.com\/columnists\/jSebastian\/2996.asp\">More Advanced XML Processing Examples<\/a><\/li>\n<li><a href=\"http:\/\/www.sqlservercentral.com\/columnists\/jSebastian\/3022.asp\">XML Workshop &#8211; FOR XML PATH<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In which Robyn and Phil decide that the best way of starting to learn XML is to jump in and take a ride around the block.&hellip;<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4252,4460,4217,4797],"coauthors":[6813,6814],"class_list":["post-278","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-t-sql-programming","tag-workbench","tag-xml","tag-xml-bcp-nodes-query-examples"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/278","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=278"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/278\/revisions"}],"predecessor-version":[{"id":103468,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/278\/revisions\/103468"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=278"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}