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:
Following is the T-SQL script which we will be used to read the XML:
DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4Â Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'
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)
Please note in above T-SQL statement XML attributes is read the same as XML Value.
Reference : Pinal Dave (https://blog.sqlauthority.com)
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
LTappSettingsGT
LT!– COPY THIS FILE TO C:\DLR –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,
Dhinakaran
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,
bharat
Vince
Lakka
Dinki
Lucy
Mac
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!
Thanks andreysolis for your comment. Glad that blog was helpful.
Thank you very much! Finally i found what i need!
I am feeling happy after your comment.
Hello great article. quick question im ruining sql server 2012 trying to inset query
INSERT INTO [dbo].[claims]
([clob]
,[processFlag])
VALUES
(
‘
—————————————————————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 ?
Thanks
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.