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
excellent
hi William,
you should assign the value in xml format.
hi sir,
while i am getting values from database to xml file using sqlserver, am able to getting only one record information.
if i want get all records information wat i have to do..
Pls give me such kind of suggestion
Post the code that you have used
xml source file: = c:\products.xml
reads:
1
Book
2
DVD
3
Video
my sql code is:
DECLARE @MyXML XML
SET @MyXML = ‘c:\products.xml’
SELECT
a.b.value(‘sku[1]/sku[1]’,’varchar(255)’) as game
FROM @MyXML.nodes(‘Product’) a(b)
Result = No Records
I’m hoping to find an answer here, I’ve tried multiple approaches. As a small aside, is there any way sql can read the remote xml file from the web, rather then only reading local versions? I’m crawling the data right now, but it would be easier to use the url in code rather then bring the xml in locally.
Products > Product > SKU are the node levels.
Sorry forgot the xml would parse, here’s another try at
showing the original file:
1
Book
2
DVD
3
Video
Hi
Is there any way to call the API in sql server. (API returns XML data). if it is possible please suggest some sample.
Thanks
Manikandan
— SQL Server 2008 execution – xquery
DECLARE @xml xml = ‘
Host=ms0001xp/Pid=1400
mani
Thanigaimani
‘
SELECT V.col.query(‘UserId’).value(‘.’, ‘VARCHAR(32)’) AS UserID,
V.col.query(‘UserName’).value(‘.’, ‘VARCHAR(32)’) AS UserName
FROM @XML.nodes(‘StudyDeleteExtendedInfo’) AS V(col);
How to read a chinese text from XML in SQL server 2008
declare @xml_ xml
set @xml_ =’false100504測試合同 ÓPCM üñ ¿¡’
SELECT
OPCM_ALTERNATIVE_NAME = T.item.value(N'(Name)[1]’, ‘NCHAR(100)’)
FROM @XML_.nodes(‘/Event/Body/Message’) AS T(item)
i am getting value ???? ÓPCM ñ¿¡ instead of 測試合同 ÓPCM üñ ¿¡
Please help to find the solution
set @xml_ =’false100504測試合同 ÓPCM üñ ¿¡’
Hello Sir,
My column datatype is nvarchar(MAX) and it stores the value:
Closed
Could you please help me how I write the sql query to get only en-US value from the column?
Thanks in anticipation.
Ragards,
Sanjay
please can somebody tell me how to read this xml file in sql server..from the starting..i am a beginner..
i need the code urgently!
OSS_SERVERS
DLGGN01
vsDataMeContext
EricssonSpecificAttributes.10.22
AEGIS2
10.155.63.92
vM.1.31
SYNCHRONISED
213
false
Ericsson
AEGIS2
RBS
CXP9014346/1_R5AV/14
SubNetwork=ONRM_RootMo_R,ManagementNode=ONRM
vsDataManagedElement
EricssonSpecificAttributes.10.22
SubNetwork=ONRM_RootMo_R,Site=RNC
RBS3418
OSS_SERVERS
DLGGN01
vsDataMeContext
EricssonSpecificAttributes.10.22
AEGIS2
10.155.63.92
vM.1.31
SYNCHRONISED
213
false
Ericsson
AEGIS2
RBS
CXP9014346/1_R5AV/14
SubNetwork=ONRM_RootMo_R,ManagementNode=ONRM
vsDataManagedElement
EricssonSpecificAttributes.10.22
SubNetwork=ONRM_RootMo_R,Site=RNC
RBS3418
Below is the sample xml file
———————————————————————————–
– – 27/06/2011 123456789 7372 CCODE001 CUSTOMER NAME N – REC ADDR 1 REC ADDR 2 WORLD SENDER NAME – SEND ADDR 1 SEND ADDR 2 SEND ADDR 3 STATE POSTCODE S CARRIER NAME 999123 BY ROAD N – 6 18 0 0 0 N 10.00 11.00 0 11.00 11.00 0 11.00 11.00 1 N N – – -ABCDEFGHIJ000000990001 -ABCDEFGHIJ000000990002 -ABCDEFGHIJ000000990003 -ABCDEFGHIJ000000990004 -ABCDEFGHIJ000000990005 -ABCDEFGHIJ000000990006 0 – T000123 CUSTOMER 6 6 – – – –
————————————————————————————————
I am importing the dat into temp table. and 1st script works fine.
CREATE TABLE dbo.[#tmp_xml_data](
[ref] [varchar](100),
[cdesc] [varchar](15),
[cno] [varchar](14),
[cdate] [varchar](10),
[cname] [varchar](50))
DECLARE @c xml
SELECT @c = BulkColumn FROM OPENROWSET(BULK ‘C:\imports\sample.xml’, SINGLE_BLOB) AS A
INSERT INTO [#tmp_xml_data]
SELECT Details.value(‘Ref[1]’,’VARCHAR(100)’),
Details.value(‘Desc[1]’,’VARCHAR(15)’),
T.C.value(‘ConNo[1]’,’VARCHAR(14)’),
T.C.value(‘ConDate[1]’,’VARCHAR(10)’),
T.C.value(‘CarrierName[1]’,’VARCHAR(50)’)
FROM @c.nodes(‘MyImportFile/Connote’) AS T(C)
CROSS APPLY @c.nodes(‘MyImportFile/Connote/RecAddr/FreightLineDetails’) AS T2(Details)
This works fine and I get the data in to my temp table
however when I modify the script (see below) to get the data from more tags then it does not works. Could you please help.
CREATE TABLE dbo.[#tmp_xml_data](
[ref] [varchar](100),
[cdesc] [varchar](15),
[cno] [varchar](14),
[cdate] [varchar](10),
[add1] [varchar](50),
[add2] [varchar](50),
[add3] [varchar](50),
[cname] [varchar](50))
DECLARE @c xml
SELECT @c = BulkColumn FROM OPENROWSET(BULK ‘C:\imports\sample.xml’, SINGLE_BLOB) AS A
INSERT INTO [#tmp_xml_data]
SELECT Details.value(‘Ref[1]’,’VARCHAR(100)’),
Details.value(‘Desc[1]’,’VARCHAR(15)’),
T.C.value(‘ConNo[1]’,’VARCHAR(14)’),
T.C.value(‘ConDate[1]’,’VARCHAR(10)’),
T.C.value(‘Add1[1]’,’VARCHAR(50)’),
T.C.value(‘Add2[1]’,’VARCHAR(50)’),
T.C.value(‘Add3[1]’,’VARCHAR(50)’),
T.C.value(‘CarrierName[1]’,’VARCHAR(50)’)
FROM @c.nodes(‘MyImportFile/Connote’) AS T(C)
CROSS APPLY @c.nodes(‘MyImportFile/Connote/RecAddr/FreightLineDetails’) AS T2(Details)
———————————————————————————————-
Could you please help.
Sample file once again the sample file.
27/06/2011
123456789
1234
CCODE001
CUSTOMER NAME
N
REC ADDR 1
REC ADDR 2
WORLD
SENDER NAME
SEND ADDR 1
SEND ADDR 2
SEND ADDR 3
STATE
POSTCODE
S
CARRIER NAME
999123
BY ROAD
N
6
18
0
0
0
N
10.00
11.00
0
11.00
11.00
0
11.00
11.00
1
N
N
ABCDEFGHIJ000000990001
ABCDEFGHIJ000000990002
0
T000123
CUSTOMER
6
6
Thanks so much for your clear concise examples!! You are a life saver.
Hi Mr. Pinal,
How can i show the following xml in select view
thanks
sharon
declare @xml xml
set @xml =
‘
222
223
224
‘
result :
SecurityLevelID ID
222 25
223 25
224 25
Hi Mr. Pinal,
How can i show the following xml in select view
thanks
sharon
declare @xml xml
set @xml =
User ID=25
SecurityLevelID 222 SecurityLevelID
SecurityLevelID 223 SecurityLevelID
SecurityLevelID 224 SecurityLevelID
UserID
result :
SecurityLevelID ID
222 25
223 25
224 25
hi, pueden ayudarme se los voy a agradecer mucho.
how create schema for this xml?
2345345
345
04/25/2011
09:12 am
Thank you Mr. Pinal
So, where is the code to actually read XML from a *FILE* — The title says “example of reading xml file” but I see nothing about how to open and read data from an XML file on disk??