In a previous article, we learned how to create an XML file using the SELECT statement in SQL Server. Today, we will explore how to read XML files using the SELECT statement.

Let’s consider the following XML file that we will read using T-SQL:

DECLARE @MyXML XML
SET @MyXML = '

  
    White
    Blue
    Black
    Green
    Red
  
  
    Apple
    Pineapple
    Grapes
    Melon
  
'

Now, let’s take a look at the T-SQL script that we will use to read the XML:

SELECT a.b.value('Colors[1]/Color1[1]', 'varchar(10)') AS Color1,
       a.b.value('Colors[1]/Color2[1]', 'varchar(10)') AS Color2,
       a.b.value('Colors[1]/Color3[1]', 'varchar(10)') AS Color3,
       a.b.value('Colors[1]/Color4[1]/@Special', 'varchar(10)') + ' ' + a.b.value('Colors[1]/Color4[1]', 'varchar(10)') AS Color4,
       a.b.value('Colors[1]/Color5[1]', 'varchar(10)') AS Color5,
       a.b.value('Fruits[1]/Fruits1[1]', 'varchar(10)') AS Fruits1,
       a.b.value('Fruits[1]/Fruits2[1]', 'varchar(10)') AS Fruits2,
       a.b.value('Fruits[1]/Fruits3[1]', 'varchar(10)') AS Fruits3,
       a.b.value('Fruits[1]/Fruits4[1]', 'varchar(10)') AS Fruits4
FROM @MyXML.nodes('SampleXML') a(b)

In the above T-SQL statement, XML attributes are read in the same way as XML values.

By using the .value() method, we can extract specific elements and attributes from the XML file. In this example, we are retrieving the values of various colors and fruits.

Remember to replace @MyXML with the actual XML file you want to read.

That’s it! Now you know how to read XML files using the SELECT statement in SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)