{"id":2473,"date":"2013-07-11T05:04:33","date_gmt":"2013-07-10T19:04:33","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/wordpress\/?p=2473"},"modified":"2013-07-11T05:04:33","modified_gmt":"2013-07-10T19:04:33","slug":"sql-server-and-xml-primer-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/sql-server-and-xml-primer-tutorial\/","title":{"rendered":"SQL Server and XML Primer Tutorial"},"content":{"rendered":"<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQLServer_XML_Primer.jpg\"><img decoding=\"async\" style=\"border: 15px solid pink;\" alt=\"SQL Server and XML Primer Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQLServer_XML_Primer.jpg\" title=\"SQL Server and XML Primer Tutorial\"  style=\"float:left;\" \/><\/a><p class=\"wp-caption-text\">SQL Server and XML Primer Tutorial<\/p><\/div>\n<p>Welcome to the SQL Server Primer Tutorial regarding XML &#8230; or SSPTrX for short (although by the time you explain it<a target=_blank href='http:\/\/grammar.quickanddirtytips.com\/double-is-double-has.aspx' title='The doubled word rule' id='doubleit' onclick='var xx=document.getElementById(\"doubleit\");  if (xx.innerHTML.indexOf(\",\") != (0 - 1)) { xx.innerHTML = \" \"; } else { xx.innerHTML = \",\"; }'>,<\/a> it is not so short anymore).<\/p>\n<blockquote><p>Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time). Its primary query languages are T-SQL and ANSI SQL.<\/p><\/blockquote>\n<p>There is no doubt that SQL Server is a great database to use and that T-SQL is an excellent tool to learn and use in conjunction with the Visual Studio suite of Visual C++,C#,VB.Net and ASP.Net products, where its integration is brilliant.   Like the hand and glove &#8230; like <a target=_blank href='https:\/\/www.youtube.com\/watch?v=YFI0rFFp8j8' title='Fred and Ginger'>Fred and Ginger<\/a>.<\/p>\n<p>In the primer tutorial you can see  <a target=_blank title='click picture' href='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQLServer_XML_Primer.jpg'>some XML functionality<\/a>.   In a later tutorial we will see SQL Server and XML and Visual Studio combined action.<\/p>\n<p>This is a big topic area and this tutorial can be supplemented by other information on the web, especially sources like this one from <a target=_blank title='SQL Server XML information by Microsoft' href='http:\/\/technet.microsoft.com\/en-us\/library\/bb522446.aspx'>Microsoft<\/a>.<\/p>\n<p>Download the input XML used in this tutorial and rename to <a target=_blank title='XML' href='http:\/\/www.rjmprogramming.com.au\/MySql\/XML\/country_state.xml_GETME'>country_state.xml<\/a> if it interests you.<\/p>\n<p>Other tutorials that use very similar data can be found <a target=_blank title='country_state.xml tutorials' href='http:\/\/www.rjmprogramming.com.au\/wordpress\/?s=country_state.xml'>here<\/a>.<\/p>\n<p>Within the T-SQL language the operators regarding XML use are:<\/p>\n<ul>\n<li>MyXml.exist(query) &#8212; returns 0,1 or NULL<\/li>\n<li>MyXml.value(query) &#8212; returns single scalar type<\/li>\n<li>MyXml.query(query) &#8212; returns XML datatype instance<\/li>\n<li>MyXml.modify(expression) &#8212; updates<\/li>\n<li>MyXml.nodes(query) &#8212; refer to tutorial parts below<\/li>\n<\/ul>\n<p>Good reference here is <a target=_blank title='XML Data Type Operators' href='http:\/\/www.wiziq.com\/tutorial\/4855-SQL-Server-2005-CLR-XML-and-T-SQL-enhancements'>this link<\/a>.<\/p>\n<p>Let&#8217;s look at some things you might want to do with T-SQL and XML below:<\/p>\n<ul>\n<li>&#8212; Create a table with an xml column (in MS-SQL, stored as <a target=_blank title='Binary large object ... information on Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Binary_large_object'>BLOB<\/a>) and import into that table, then query it\n<p>CREATE TABLE entity (IntCol int, XmlCol xml);<br \/>\nGO<\/p>\n<p>INSERT INTO entity(XmlCol)<br \/>\n SELECT * FROM OPENROWSET(<br \/>\n  BULK &#8216;c:country_state.xml&#8217;,<br \/>\n  SINGLE_BLOB) AS x;<br \/>\nGO<\/p>\n<p>SELECT * from entity<\/p>\n<p><img src='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQL_Server_Primer_Bits-0.jpg' title='Results' alt='Results' \/><\/p>\n<li>&#8212; As above, create a table with an xml column (in MS-SQL, stored as <a target=_blank title='Binary large object ... information on Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Binary_large_object'>BLOB<\/a>), but, and this is a good idea here (<a target=_blank title='Read more about the pros and cons of autoincrementing database key fields here' href='http:\/\/stackoverflow.com\/questions\/1997358\/pros-and-cons-of-autoincrement-keys-on-every-table'>probably<\/a> preferable to approach above), add an IDENTITY and PRIMARY KEY to int column (like AUTO_INCREMENT in MySql), and import into that table, then query it\n<p>CREATE TABLE entity_auto (IntCol int IDENTITY PRIMARY KEY, XmlCol xml);<br \/>\nGO<\/p>\n<p>INSERT INTO entity_auto(XmlCol)<br \/>\n SELECT * FROM OPENROWSET(<br \/>\n  BULK &#8216;c:country_state.xml&#8217;,<br \/>\n  SINGLE_BLOB) AS x;<br \/>\nGO<\/p>\n<p>SELECT * from entity_auto<\/p>\n<p><img src='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQL_Server_Primer_Bits-00.jpg' title='Results' alt='Results' \/>\n<\/li>\n<li> &#8212; Import c:country_state.xml into VARCHAR variable FileContents (thanks to great advice of <a target=_blank title='XML to variable' href='http:\/\/www.techrepublic.com\/article\/read-an-xml-file-from-sql-server\/5796532'>link1<\/a> and <a target=_blank title='Priviledge' href='http:\/\/stackoverflow.com\/questions\/5131491\/enable-xp-cmdshell-sql-server'>link2<\/a>) then do queries<br \/>\n<span style='color: red;'>&#8212; Parts in red are particular to my case&#8217;s database structure<\/span><br \/>\n<span style='color: brown;'>&#8212; Follow up queries particular to my case&#8217;s database structure<\/span><br \/>\n<span style='color: blue;'>&#8212; Parts to do with priviledge that may not be necessary, and\/or, alas, may not be allowed<br \/>\n&#8212; To allow advanced options to be changed.<br \/>\nEXEC sp_configure &#8216;show advanced options&#8217;, 1<br \/>\nGO<br \/>\n&#8212; To update the currently configured value for advanced options.<br \/>\nRECONFIGURE<br \/>\nGO<br \/>\n&#8212; To enable the feature.<br \/>\nEXEC sp_configure &#8216;xp_cmdshell&#8217;, 1<br \/>\nGO<br \/>\n&#8212; To update the currently configured value for this feature.<br \/>\nRECONFIGURE<br \/>\nGO<br \/>\n<\/span><br \/>\nDECLARE @FileName varchar(255)<br \/>\nDECLARE @ExecCmd VARCHAR(255)<br \/>\nDECLARE @y INT<br \/>\nDECLARE @x INT<br \/>\nDECLARE @one INT<br \/>\n<span style='color: red;'>DECLARE @cname VARCHAR(255)<br \/>\nDECLARE @ccapital VARCHAR(255)<br \/>\nDECLARE @carea VARCHAR(255)<br \/>\nDECLARE @cpopulation VARCHAR(255)<br \/>\nDECLARE @onestate INT<br \/>\nDECLARE @oneterritory INT<br \/>\nDECLARE @oneexternal INT<br \/>\nDECLARE @MyXml XML<\/span><br \/>\nDECLARE @FileContents VARCHAR(8000)<br \/>\nDECLARE @csql VARCHAR(8000)<\/p>\n<p>CREATE TABLE #temp2XML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))<\/p>\n<p>SET @FileName = &#8216;<span style='color:red;'>C:country_state.xml<\/span>&#8216;<br \/>\nSET @ExecCmd = &#8216;type &#8216; + @FileName + &#8216; | find \/V &#8220;?xml&#8221;&#8216;<br \/>\nSET @FileContents = &#8221;<\/p>\n<p>INSERT INTO #temp2XML EXEC master.dbo.xp_cmdshell @ExecCmd<br \/>\nSELECT @y = count(*) from #temp2XML<\/p>\n<p>SET @x = 0<br \/>\nWHILE @x <> @y<br \/>\n    BEGIN<br \/>\n        SET @x = @x + 1<br \/>\n        SELECT @FileContents = @FileContents + ThisLine from #temp2XML WHERE PK = @x and PK < @y\n    END\n\n--SELECT @FileContents\n--SELECT * FROM #temp2XML\n\n<span style='color: red;'>SELECT @onestate = count(*) from #temp2XML WHERE ThisLine like &#8216;%\/STATE%&#8217;<br \/>\nSELECT @oneexternal = count(*) from #temp2XML WHERE ThisLine like &#8216;%\/EXTERNAL%&#8217;<br \/>\nSELECT @oneterritory = count(*) from #temp2XML WHERE ThisLine like &#8216;%\/TERRITORY%&#8217;<\/span><\/p>\n<p>DROP TABLE #temp2XML<\/p>\n<p><span style='color: red;'>SET @MyXml = @FileContents<\/span><\/p>\n<p><span style='color: brown;'><br \/>\n&#8212; One column output of all data at STATE\/TERRITORY\/EXTERNAL hierarchy<br \/>\n<\/span><br \/>\n<span style='color: brown;'><br \/>\nSELECT @MyXml.query(<br \/>\n &#8216;for $x in \/\/STATE\/*<br \/>\n return data($x)&#8217;)<br \/>\n UNION ALL<br \/>\n SELECT @MyXml.query(<br \/>\n &#8216;for $x in \/\/TERRITORY\/*<br \/>\n return data($x)&#8217;)<br \/>\n UNION ALL<br \/>\n SELECT @MyXml.query(<br \/>\n &#8216;for $x in \/\/EXTERNAL\/*<br \/>\n return data($x)&#8217;);<\/span><br \/>\n<img src='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQL_Server_Primer_Bits-1.jpg' title='Results' alt='Results' \/><br \/>\n<span style='color: brown;'><br \/>\n&#8212; Multi column output of data at COUNTRY then STATE\/TERRITORY\/EXTERNAL hierarchy<br \/>\n&#8212; Unwieldy nature of this can not be fixed here by parametizing the [] numbers but maybe by other approaches?!<br \/>\n<\/span><br \/>\n<img src='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/XML\/SQL_Server_Primer_Bits-2.jpg' title='Results' alt='Results' \/><\/p>\n<p><span style='color: brown;'><br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[1]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[1]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[1]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[1]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[2]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[2]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[2]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[2]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[3]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[3]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[3]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[3]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[4]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[4]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[4]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[4]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[5]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[5]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[5]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[5]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[6]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[6]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[6]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[6]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[7]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[7]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[7]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[7]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[9]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[9]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[9]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[9]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[10]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[10]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[10]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[10]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[11]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[11]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[11]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[11]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;State&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(STATE)[12]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(STATE)[12]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(STATE)[12]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(STATE)[12]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[1]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[1]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[1]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[1]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[2]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[2]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[2]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[2]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[3]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[3]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[3]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[3]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[4]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[4]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[4]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[4]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[5]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[5]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[5]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[5]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[6]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[6]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[6]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[6]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[7]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[7]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[7]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[7]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[9]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[9]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[9]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[9]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[10]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[10]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[10]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[10]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[11]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[11]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[11]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[11]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;Territory&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(TERRITORY)[12]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(TERRITORY)[12]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(TERRITORY)[12]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(TERRITORY)[12]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[1]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[1]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[1]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[1]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[2]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[2]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[2]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[2]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[3]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[3]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[3]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[3]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[4]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[4]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[4]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[4]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[5]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[5]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[5]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[5]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[6]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[6]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[6]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[6]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[7]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[7]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[7]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[7]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[9]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[9]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[9]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[9]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[10]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[10]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[10]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[10]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[11]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[11]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[11]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[11]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n UNION<br \/>\nSELECT Country = T.item.value(&#8216;CNAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),  &#8216;External&#8217; As Type,<br \/>\nName = T.item.value(&#8216;(EXTERNAL)[12]\/NAME[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nCapital = T.item.value(&#8216;(EXTERNAL)[12]\/CAPITAL[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nArea = T.item.value(&#8216;(EXTERNAL)[12]\/AREA[1]&#8217;, &#8216;VARCHAR(255)&#8217;),<br \/>\nPopulation = T.item.value(&#8216;(EXTERNAL)[12]\/POPULATION[1]&#8217;, &#8216;VARCHAR(255)&#8217;)<br \/>\nFROM @MyXml.nodes(&#8216;\/INFO\/COUNTRY&#8217;) AS T(item)<br \/>\n<\/span>\n<\/li>\n<\/ul>\n<p>Link to SQL Server more information &#8230; <a target=_blank href='http:\/\/en.wikipedia.org\/wiki\/Microsoft_SQL_Server' title='Link to SQL Server more information'>via Wikipedia, from where quote above came<\/a>.<br \/>\nLink to SQL Server &#8220;spiritual home&#8221; &#8230; <a target=_blank href='http:\/\/www.microsoft.com\/en-us\/sqlserver\/product-info.aspx' title='As the foundation of the cloud-ready information platform, SQL Server 2012 will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by mission critical confidence. '>via Microsoft, includes talk about (the recent) SQL Server 2012, and about download options<\/a>.<br \/>\nLink to SQL Server jobs done &#8230; <a target=_blank href='http:\/\/www.rjmprogramming.com.au\/MS-SQL\/' title='Examples of use of SQL Server'>SQL Server jobs done<\/a> &#8230; some of personal experience.\n<\/p>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d2473' onclick='var dv=document.getElementById(\"d2473\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"http:\/\/www.rjmprogramming.com.au\/wordpress\/?cat=47\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d2473' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Welcome to the SQL Server Primer Tutorial regarding XML &#8230; or SSPTrX for short (although by the time you explain it, it is not so short anymore). Microsoft SQL Server is a relational database management system developed by Microsoft. As &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/sql-server-and-xml-primer-tutorial\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12,37],"tags":[299,819,1187,1319,1480],"class_list":["post-2473","post","type-post","status-publish","format-standard","hentry","category-database","category-elearning","category-tutorials","tag-database-2","tag-ms-sql","tag-sql-server","tag-tutorial","tag-xml"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/2473"}],"collection":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/comments?post=2473"}],"version-history":[{"count":0,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/2473\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=2473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=2473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=2473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}