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
Hello sir,
The same is occured here. How to rectify it.
Thanks
Puneet
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ‘‘’.
hello give proper single quotes
Hi Pinal,
Along with the ‘nodes’ funtion we do have OpenXML in SQL Server for reading the XML data.
So what is the difference between these two or which one is better in terms of performance?
-Munish Bansal
Dear Pinal
Please tell me how to read multiple rows. like
DECLARE @MyXML XML
SET @MyXML = ‘
White
Blue
Black
Green
Red
Orange
Yello
Pink
Purple
Indigo
Apple
Pineapple
Grapes
Melon
‘
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)
UNION ALL
SELECT
a.b.value(‘Colors[2]/Color1[1]’,’varchar(10)’) AS Color1,
a.b.value(‘Colors[2]/Color2[1]’,’varchar(10)’) AS Color2,
a.b.value(‘Colors[2]/Color3[1]’,’varchar(10)’) AS Color3,
a.b.value(‘Colors[2]/Color4[1]’,’varchar(10)’) AS Color4,
a.b.value(‘Colors[2]/Color5[1]’,’varchar(10)’) AS Color5,
” AS Fruits1,
” AS Fruits2,
” AS Fruits3,
” AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b)
Is this correct way? how i can handle if i have 100 or many rows?
Thanks & Regards
Smita
DECLARE @productIds xml
SET @productIds =’3615′
SELECT
ParamValues.ID.value(‘.’,’VARCHAR(20)’)
FROM @productIds.nodes(‘/Products/id’) as ParamValues(ID)
>>Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
This is a text encoding issue with Pinal’s examples. The ‘ character is encoded incorrectly for SSMS. You need to change the quotes in Pinal’s posts from ’ to ‘ and from ” to “. At least if you are using the 1033 codepage — not sure otherwise.
You could also do this if you just want all the values back:
SELECT @MyXML.query(
‘for $x in //Colors/*
return data($x)’)
UNION ALL
SELECT @MyXML.query(
‘for $x in //Fruits/*
return data($x)’);
If you want to put the xml values in columns, I usually do it like this:
SELECT
Color1 = T.item.value(‘(Color1)[1]’, ‘CHAR(10)’)
FROM @MyXML.nodes(‘SampleXML/Colors’) AS T(item);
Or like this:
SELECT
Color1 = T.item.query(‘Color1’).value(‘.’, ‘CHAR(10)’)
FROM @MyXML.nodes(‘SampleXML/Colors’) AS T(item);
Hi Pinal,
How can i parse XML,which is send as a input parameter into stored procedure.
hi ..
can u please help me to solve my probleme
i am studying SQL in relation with XML and i must imoprt a XML file to SQL table .. can u help me in that …
hello sir,
can i load table attibute with its value in xml file using sql.
if yes pls give me example query.
Hello Sir,
how can i load xml file in perticular table or different tables.
pls help us.
Good one.
Mr. Pinal,
How can I find out the count if there is more than one parents?
Thanks
~Philip
I need to get the value of {{BOLID}}
Thanks again,
Sjothi
Hi,
I would like to read an xml and get a value from it but the xml file is returned via the following url:
where MyPostalCode is a postal code
MyGoogleMapAPI_Key is my google Map Api key that I received when registered.
To read the xml file, I used the folowing code :
select * FROM OPENROWSET(BULK ‘http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key’,SINGLE_BLOB) as c
but I am getting the following error:
Cannot bulk load because the file… could not be opened. Operating system error code 123(error not found).
Do you know how to fix this issue?
@Benn,
If you are using SQL 2005 and above version, then you can get values by this query:
DECLARE @XML XML
SET @XML = ‘
MyPostalCode
602geocode
‘
SELECT d.v.value(‘../name[1]’,’VARCHAR(MAX)’),
d.v.value(‘code[1]’,’VARCHAR(MAX)’),
d.v.value(‘request[1]’,’VARCHAR(MAX)’)
FROM @XML.nodes(‘/kml/Response/Status’) d(v)
Let me know if it helps you.
Thanks,
Tejas
SQLYoga.com
Hi Tejas,
Thank you for your quick response. I have a question, where do I put the httpMyGoogleMapAPI_Key or the following code:
http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key’
I mean does your code woks to get the xml data from google map api?
thanks a lot. This will really help me.
@Benn,
You just need to pass the XML you get from Google API to DB.
I did the same, I manually copied xml and give you query.
You can enter httpMyGoogleMapAPI_Key in any element where you want. You can access it from SQL.
Tejas
SQLYoga.com
Hi Tejas,
Well… ok… my descrition was not complet. I’ll try to give you all requested information to help me fine the solution.
I already have a table in my database let’s say T_RESTO with the following fields:
T_RESTO(Name,Address, PostalCode) let’s keep it like that.
and I have an interface where a user can enter a postalcode to fine Resto near the given postal code.
I would like to create a T-Sql function that will have one parameter, the PostalCode entered by a user, and the function should returned a table with the following fields:
– Resto’s Name
– Address
– PostalCode
– Distance between the Resto’s postal code and the postal code entered by user.
My point was to start creating a T-Sql that will returned the coordinates for a given PostalCode. These coordinates will be taken from Google Maps API.
Does this make sens to you?
Benn
Hi Pinal Dave
help me Out to become Best Sql DBA
Thanks & Regards
Chandrakant Singh
Please provide sample code to use bcp export data from table/view to xml file.
Thanks.
Multi line selection:
DECLARE @MyXML XML
SET @MyXML = ‘
White
Blue
Black
Green
Red
White1
Blue1
Black1
Green1
Red1
White2
Blue2
Black2
Green2
Red2
‘
SELECT
a.b.value(‘Color1[1]’,’varchar(10)’) AS Color1,
a.b.value(‘Color2[1]’,’varchar(10)’) AS Color2,
a.b.value(‘Color3[1]’,’varchar(10)’) AS Color3,
a.b.value(‘Color4[1]/@Special’,’varchar(10)’)+’ ‘+ a.b.value(‘Color4[1]’,’varchar(10)’) AS Color4,
a.b.value(‘Color5[1]’,’varchar(10)’) AS Color5
FROM @MyXML.nodes(‘SampleXML/Colors’) a(b)