SQL SERVER – Simple Example of Reading XML File Using T-SQL

In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.

Following is the XML which we will read using T-SQL:

SQL SERVER - Simple Example of Reading XML File Using T-SQL samplexml

Following is the T-SQL script which we will be used to read the XML:

SET @MyXML = '<SampleXML>
<Color4 Special="Light">Green</Color4>

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)

Please note in above T-SQL statement XML attributes is read the same as XML Value.

SQL SERVER - Simple Example of Reading XML File Using T-SQL samplexml2

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

SQL Scripts, SQL XML
Previous Post
SQL SERVER – Simple Example of Creating XML File Using T-SQL
Next Post
SQL SERVER – Restore Master Database – An Easy Solution

Related Posts

94 Comments. Leave new

  • you are perfect. very thanks.

  • Sir

    Help me in getting the text of a particular value attribute

  • Dear

    i have one employee table in xml
    how to display the all records from xml
    like(select *from emp)

  • Hi Dave,

    I have a Stored procedure that reads XML file using the following query

    INSERT INTO inv_tempfile(BulkColumn)
    select BulkColumn from Openrowset(
    Bulk ”’ + @FileName + ”’, Single_Blob) as tt

    This query reads the entire content of the file into inv_tempfile. The BulkColumn is a VARCHAR(MAX) type.

    But the problem is, if the XML file contains “?” in any of its elements. Then this query is unable to read the whole file content.

    below is the sample XML Element file.

    #805 ? 2988 Alder Street, Vancouver tario C0B1C0

    Can u please explain me why this is happening and if there is any workaround.

  • “XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *'”
    sir when i write the above command it shows me this error.

    • Use This
      SELECT xCol.value(‘(//author/last-name)[1]’, ‘nvarchar(50)’) LastName
      FROM docs

  • I’m trying to pull a single int from an xml file stored on an app server into a stored proc, but I’m not sure how that would work even after reading all of the examples above. I’d need to reference the file as \\[app server name]\c$\[path]\[filename].xml. Also, the int in the xml file is not stored within a tag, but is instead a key. Here is an excerpt of the xml file up to the point of the int that I need to pull in (I want 22 to be returned to the stored proc):

  • looks like the xml won’t post. trying again replacing with LT & GT:

    LT?xml version=”1.0″ encoding=”utf-8″ GT
    LT!– Change the pieces to run to true –GT
    LT!– InputFeeds –GT
    LTadd key=”LoadPLPD” value = “false” /GT
    LT!– LoadPLPDCancelDays is used to calculate the cancel date and should be a value greater than zero/GT
    LTadd key=”LoadPLPDCancelDays” value = “22” /GT

  • Hi, how to read the xml in sql server 2005. Below my code is placed,

    declare @xml xml

    set @xml=

    01 first

    02 Second

    select a.b.value(‘.’, ‘nvarchar(50)’) as a_value
    from @xml.nodes(‘parent/child /subChild’) a(b)

  • Hi pinal..
    I have a serious issues.. we are getting data’s daily in a XML format and we need to insert all these data’s into SQL SERVER. The problem is, we have a column called ‘Amount’ and same name we are maintaining in our SQL server.

    Sometimes, we are receiving XML files, with ‘Amount’ column name changed as ‘Amt’ and without checking if we load it, we are inserted with Null values.

    So I need to solution, that my table should accept AMOUNT as well as AMT as column name.

    Can you let me know the solution please.

    Thanks and regards,

  • How can I do if the XML file has namespace?

  • Hello sir,

    how to read xml file from url in sql server 2008 and insert into table?

    Thank You,

  • Vince

    Hi Pinal / Members,

    I have a questin for you, how to read a file witohut using static mathod, the list is dynamic and it can be any numbers from 1 to 100s. I tried using static variables and its bit messy, there must be a proper way to read and convert this XML file to SQL

  • Hello, would you explain me why do you use this symbols ‘ [ ‘, ‘ ] ‘ and why do you use the number 1 between them, please

  • how can alter the type in sql server 2008

  • Thank you very much! I have 3 hours searching over internet! Finally found your solution! There is a lot examples using common xml structures!

  • Thank you very much! Finally i found what i need!

  • Hello great article. quick question im ruining sql server 2012 trying to inset query
    INSERT INTO [dbo].[claims]

    —————————————————————end code —-
    however my it is trowing an exception due the special char ññ in the name.
    is any way that we can ignore the first line ‘ i think the problem is with the encoding. ?
    or change the encoding to ignore. ?

  • Hi Pinal Dave,

    I am looking some of solution to work with xml.

    I have one table with two columns : 1. ID (nvarchar) 2. XmlData (xml)

    Table have three rows.

    The stored xml have company node.

    Expected Result:
    I need comma separated company against each id .How I can get ?


  • so how you read the xml FILE?
    this is reading xml string!!!!!

  • The title of your article is miss leading. As others have mentioned, the script is creating a variable of type XML, adding data to it, and then running a select statement using xquery to parse the xml.
    Your article should also have an example of reading an actual file from disk that contains xml.


Leave a Reply