SQL Server and XML Primer Tutorial

Posted on by
SQL Server and XML Primer Tutorial

SQL Server and XML Primer Tutorial

Welcome to the SQL Server Primer Tutorial regarding XML … 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 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.

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 … like Fred and Ginger.

In the primer tutorial you can see some XML functionality. In a later tutorial we will see SQL Server and XML and Visual Studio combined action.

This is a big topic area and this tutorial can be supplemented by other information on the web, especially sources like this one from Microsoft.

Download the input XML used in this tutorial and rename to country_state.xml if it interests you.

Other tutorials that use very similar data can be found here.

Within the T-SQL language the operators regarding XML use are:

  • MyXml.exist(query) — returns 0,1 or NULL
  • MyXml.value(query) — returns single scalar type
  • MyXml.query(query) — returns XML datatype instance
  • MyXml.modify(expression) — updates
  • MyXml.nodes(query) — refer to tutorial parts below

Good reference here is this link.

Let’s look at some things you might want to do with T-SQL and XML below:

  • — Create a table with an xml column (in MS-SQL, stored as BLOB) and import into that table, then query it

    CREATE TABLE entity (IntCol int, XmlCol xml);
    GO

    INSERT INTO entity(XmlCol)
    SELECT * FROM OPENROWSET(
    BULK ‘c:country_state.xml’,
    SINGLE_BLOB) AS x;
    GO

    SELECT * from entity

    Results

  • — As above, create a table with an xml column (in MS-SQL, stored as BLOB), but, and this is a good idea here (probably 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

    CREATE TABLE entity_auto (IntCol int IDENTITY PRIMARY KEY, XmlCol xml);
    GO

    INSERT INTO entity_auto(XmlCol)
    SELECT * FROM OPENROWSET(
    BULK ‘c:country_state.xml’,
    SINGLE_BLOB) AS x;
    GO

    SELECT * from entity_auto

    Results

  • — Import c:country_state.xml into VARCHAR variable FileContents (thanks to great advice of link1 and link2) then do queries
    — Parts in red are particular to my case’s database structure
    — Follow up queries particular to my case’s database structure
    — Parts to do with priviledge that may not be necessary, and/or, alas, may not be allowed
    — To allow advanced options to be changed.
    EXEC sp_configure ‘show advanced options’, 1
    GO
    — To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    — To enable the feature.
    EXEC sp_configure ‘xp_cmdshell’, 1
    GO
    — To update the currently configured value for this feature.
    RECONFIGURE
    GO

    DECLARE @FileName varchar(255)
    DECLARE @ExecCmd VARCHAR(255)
    DECLARE @y INT
    DECLARE @x INT
    DECLARE @one INT
    DECLARE @cname VARCHAR(255)
    DECLARE @ccapital VARCHAR(255)
    DECLARE @carea VARCHAR(255)
    DECLARE @cpopulation VARCHAR(255)
    DECLARE @onestate INT
    DECLARE @oneterritory INT
    DECLARE @oneexternal INT
    DECLARE @MyXml XML
    DECLARE @FileContents VARCHAR(8000)
    DECLARE @csql VARCHAR(8000)

    CREATE TABLE #temp2XML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

    SET @FileName = ‘C:country_state.xml‘
    SET @ExecCmd = ‘type ‘ + @FileName + ‘ | find /V “?xml”‘
    SET @FileContents = ”

    INSERT INTO #temp2XML EXEC master.dbo.xp_cmdshell @ExecCmd
    SELECT @y = count(*) from #temp2XML

    SET @x = 0
    WHILE @x <> @y
    BEGIN
    SET @x = @x + 1
    SELECT @FileContents = @FileContents + ThisLine from #temp2XML WHERE PK = @x and PK < @y END --SELECT @FileContents --SELECT * FROM #temp2XML SELECT @onestate = count(*) from #temp2XML WHERE ThisLine like ‘%/STATE%’
    SELECT @oneexternal = count(*) from #temp2XML WHERE ThisLine like ‘%/EXTERNAL%’
    SELECT @oneterritory = count(*) from #temp2XML WHERE ThisLine like ‘%/TERRITORY%’

    DROP TABLE #temp2XML

    SET @MyXml = @FileContents


    — One column output of all data at STATE/TERRITORY/EXTERNAL hierarchy


    SELECT @MyXml.query(
    ‘for $x in //STATE/*
    return data($x)’)
    UNION ALL
    SELECT @MyXml.query(
    ‘for $x in //TERRITORY/*
    return data($x)’)
    UNION ALL
    SELECT @MyXml.query(
    ‘for $x in //EXTERNAL/*
    return data($x)’);
    Results

    — Multi column output of data at COUNTRY then STATE/TERRITORY/EXTERNAL hierarchy
    — Unwieldy nature of this can not be fixed here by parametizing the [] numbers but maybe by other approaches?!

    Results


    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[1]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[1]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[1]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[1]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[2]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[2]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[2]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[2]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[3]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[3]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[3]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[3]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[4]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[4]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[4]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[4]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[5]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[5]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[5]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[5]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[6]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[6]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[6]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[6]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[7]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[7]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[7]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[7]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[9]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[9]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[9]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[9]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[10]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[10]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[10]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[10]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[11]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[11]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[11]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[11]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘State’ As Type,
    Name = T.item.value(‘(STATE)[12]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(STATE)[12]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(STATE)[12]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(STATE)[12]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[1]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[1]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[1]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[1]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[2]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[2]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[2]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[2]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[3]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[3]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[3]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[3]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[4]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[4]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[4]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[4]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[5]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[5]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[5]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[5]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[6]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[6]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[6]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[6]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[7]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[7]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[7]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[7]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[9]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[9]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[9]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[9]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[10]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[10]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[10]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[10]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[11]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[11]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[11]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[11]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘Territory’ As Type,
    Name = T.item.value(‘(TERRITORY)[12]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(TERRITORY)[12]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(TERRITORY)[12]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(TERRITORY)[12]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[1]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[1]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[1]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[1]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[2]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[2]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[2]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[2]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[3]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[3]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[3]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[3]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[4]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[4]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[4]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[4]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[5]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[5]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[5]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[5]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[6]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[6]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[6]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[6]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[7]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[7]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[7]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[7]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[9]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[9]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[9]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[9]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[10]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[10]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[10]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[10]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[11]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[11]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[11]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[11]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)
    UNION
    SELECT Country = T.item.value(‘CNAME[1]’, ‘VARCHAR(255)’), ‘External’ As Type,
    Name = T.item.value(‘(EXTERNAL)[12]/NAME[1]’, ‘VARCHAR(255)’),
    Capital = T.item.value(‘(EXTERNAL)[12]/CAPITAL[1]’, ‘VARCHAR(255)’),
    Area = T.item.value(‘(EXTERNAL)[12]/AREA[1]’, ‘VARCHAR(255)’),
    Population = T.item.value(‘(EXTERNAL)[12]/POPULATION[1]’, ‘VARCHAR(255)’)
    FROM @MyXml.nodes(‘/INFO/COUNTRY’) AS T(item)

Link to SQL Server more information … via Wikipedia, from where quote above came.
Link to SQL Server “spiritual home” … via Microsoft, includes talk about (the recent) SQL Server 2012, and about download options.
Link to SQL Server jobs done … SQL Server jobs done … some of personal experience.

If this was interesting you may be interested in this too.

This entry was posted in Database, eLearning, Tutorials and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *