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
Hi Tejas,
DECLARE @XML XML
SET @XML = ‘
MyPostalCode
‘
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)
select * FROM OPENROWSET(BULK ‘http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key’,SINGLE_BLOB) as c
This gif me error
(0 row(s) affected)
Msg 4861, Level 16, State 1, Line 12
Cannot bulk load because the file “http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key” could not be opened. Operating system error code 123(error not found).
hi , anyone tell me when we have to define for xml reading value through this code then i am getting error in xmlrd.Read(); this position.
example:–
XmlTextReader xmlrd = new XmlTextReader(“c:\\books1.xml”);
xmlrd.Read();
while (xmlrd.Read())
{
string val = xmlrd.Read().ToString();
//string val1 = xmlrd.Name;
xmlrd.Close();
}
Hi,
I have excel data base,want to create this database into xml format and import this xml data into sql server enterprise edition.
how can i do this.
pls explian with screen shots
its very usefull for my all friends
pls urgent
hello sir ,
what will be the select query if we have ‘N’ number of colors tag ….. plz reply thank you
Dear sir,
Pls explain in detail about to install sql server 2005 enterprise edition on ubuntu.
pls tell me the procedure in step by step
thanks
vinoth
Hello Pinal,
I am trying to OPENROWSET (BULK…) a XML file. It works except my server takes 5 mins to load a 18MB file. I have a dedicated server, SQL 2005 express sp3.
Should it take this long? All the research I have read, people are reporting over 100MB files loading in less than 1 min.
I didnt want to post my script here, its kinda long but would be glad to send it to you, or post.
Thank you for help!
Jason
Hi…We are using OpenXML in one of our SPs….We took reference form following code given in some website…
CREATE PROCEDURE update_authors_OPENXML (
@xml_text TEXT)
AS
SET NOCOUNT ON
— document handle:
DECLARE @i INT
— create internal representation
— of the document
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
— run an update:
UPDATE authors
SET au_lname = b.au_lname,
au_fname = b.au_fname,
address = b.address,
city = b.city,
state = b.state,
zip = b.zip,
contract = b.contract
FROM authors a INNER JOIN
OPENXML(@i, ‘/root/authors’)
WITH authors b
ON a.au_id = b.au_id
But we are facing while using above syntax with our table….because the Column which we are using for Inner Join (a.au_id = b.au_id as in above case) is Identity column….and the above syntax gives error…It does not select the Identity column from the table….Instead it shows ‘Invalid Column name’ error.
Can you suggests what is wrong ….or any other way through which we can get the Identity column with above code….
When I removed the Identity property from the table and tested then it works fine…but the issue is with Identity property….
I would be very grateful to you if you could provide any help regarding this issue….
Kinjal Shah
Hi Pinal Dave,
I would like to insert a xml file into a table using queries and then want to read values inside the xml file. How can i do it, Please provide me a sample example so that i can able to learn it.
Thanks
Regards
Ganesh.
Hi,
We are planning to shift our enterprise application to windows azure platform…
but we have critical issue where we are using XMLNAMESPACES in attribute level … since sql azure is not supporting this syntax what can be the best alternative or will it be supported in future sql azure features…..
this is business based enterprise application developed based on client requirements we cannot change the xml format.. so please provide best alternative ..please see the sample below
DECLARE @x xml ,
@L_C VARCHAR(10),
@l_cnt numeric(10),
@doc int
BEGIN
SET @x=’
‘
;WITH XMLNAMESPACES (‘your:namespace’ as “CT”)
SELECT c.value(‘@CT:I’,’NUMERIC(10)’) AS CT_I,
c.value(‘@C’,’NUMERIC(10)’) AS C FROM @x.nodes(‘//F’) AS T(c)
END
–Create Parent Table
CREATE TABLE [dbo].[Parent]
(
[Parent_ID] [int] IDENTITY(1,1) primary key clustered,
[InternalID] [int] NULL,
[Action] [nvarchar](255) NULL,
[InsertUserID] [nvarchar](255) NULL,
[InsertDateTime] [nvarchar](255) NULL,
[UpdateUserID] [nvarchar](255) NULL
)
–Create Child Table
CREATE TABLE [dbo].[Child]
(
[Child_ID] [int] IDENTITY(1,1) primary key clustered,
[Parent_ID] int references Parent(Parent_ID),
[InternalID] [int] NULL,
[Action] [nvarchar](255) NULL,
[InsertUserID] [nvarchar](255) NULL,
[InsertDateTime] [nvarchar](255) NULL,
[UpdateUserID] [nvarchar](255) NULL
)
–Create KidTable
CREATE TABLE [dbo].[Kid]
(
[Kid_ID] [int] IDENTITY(1,1) primary key clustered,
[Child_ID] int references Child(Child_ID),
[InternalID] [int] NULL,
[Action] [nvarchar](255) NULL,
[InsertUserID] [nvarchar](255) NULL,
[InsertDateTime] [nvarchar](255) NULL,
[UpdateUserID] [nvarchar](255) NULL
)
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
‘
ASDZXCCFG
08-29-2008 17:13:57
SYSTEM_USER
13-05-2010 13:22:41
Child1
02-20-2008 16:27:54
SYSTEM_USER
11-05-2009 15:22:44
Kid1
02-20-2008 16:27:54
CATS
02-22-2008 12:12:43
KId2
02-20-2008 16:27:54
CATS
02-22-2008 12:12:43
Child2
08-20-2008 16:27:54
SYSTEM_USER
11-05-2009 15:22:44
Kid3
02-20-2008 16:27:54
CATS
02-22-2008 12:12:43
‘
—–Insert into Parent Table
Insert into Parent
SELECT *
FROM OPENXML(@hDoc, ‘/Parent’,3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
—————————-
Declare @Parent_ID INT
SELECT @Parent_ID = @@IDENTITY
————————-
–Insert into Child table
Insert into Child
select @Parent_ID,*
FROM OPENXML(@hDoc, ‘/Parent/Childs/Child’,3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
declare @Child_ID int
select @Child_ID = @@identity
–Insert into Request table
Insert into Kid
select @Child_ID,*
FROM OPENXML(@hDoc, ‘/Parent/Childs/Child/Kids/Kid’,3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
——————————————————
select * from Parent
select * from Child
select * from Kid
I am trying to load the XML data into SQL server table.
Below is the structure for my table I have Identity column in each table and I want to pass that Identity value to the subnode. But when I am trying to load the third level(Kid table) , I am just getting the last Identity value from the Child Table into the Kid table.
I need Child_ID 1 for InsertUserID(Kid1,Kid2) and Child_ID 2 for InsertUserID(Kid3) in the Child Table.
Is there any way that i can get the populate the one row in Child table and get the identity of that and populate the Kid table associate with that Child_ID , and again same for the second row in the Child table.
—Below is the Expected result for the Kid Table
Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
1 1 3406436 Insert Kid1 02-20-2008 CATS
2 1 3406437 Update KId2 02-20-2008 CATS
3 2 3406438 None Kid3 02-20-2008 CATS
–But I am getting the thisChild_ID 2 for all the records in Kid Table)
Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
1 2 3406436 Insert Kid1 02-20-2008 CATS
2 2 3406437 Update KId2 02-20-2008 CATS
3 2 3406438 None Kid3 02-20-2008 CATS
Hello Pinal, I need a help.
I have a table using SQL2k5 and in one table I have a column xml tagged like
‘JXY1XY1ABC INSURANCE
I would like to strip the columns from “ID” onwards in a variable so that I can use it in a differet query. Could you please help me out, I am unable to get the ID value.
Tx
Ram
Sir I have this XML structure:
Declare @xmlData XML = ‘
ABC
DEF
GHI
JKL
MNO
PQR
‘
SELECT d.c.value(‘StudyDesign[1]’,’nvarchar(MAX)’) as StudyDesign
FROM @xmlData.nodes(‘/Study/Study_Design’) d(c)
This just returns me the 1st node data. I want all the nodes of StudyDesign in the table.
Please assist me out of this problem.
With Regards
Hi Niladri,
To achive this you will have to use text()[1] property and one more thing is you will have to give full path till your child node just like following
SELECT d.c.value(‘text()[1]’,’nvarchar(MAX)’) as StudyDesign
FROM @xmlData.nodes(‘/Study/Study_Design/StudyDesign’) d(c)
you can see your StudyDesign is child node and i have removed it and put it in path
i hope you got it…
Thanks
Sorry, the xml did not get pasted properly earlier
HEADER1
HEADER2
HEADER3
HEADER4
rank1
rank2
Value1
rank3
Value2
rank4
Value3
hi
hi just remove that ` till symbol and put ‘ single quetation it will work thanks
I need your help to get this query to output like:
DECLARE @xmlDoc xml
SET @xmlDoc = ‘
'42-211'
'42-211- – '
‘
select
a.b.value(‘Old[1]’,’varchar(20)’) as old,
a.b.value(‘New[1]’,’varchar(20)’) as new
from @xmldoc.nodes(‘well’)a(b)
Output:
Column Name Old New
I need your help to get the query below to output like below. Also, the CoumnName will change daily, how do I get it to pick it up automatically?
DECLARE @xmlDoc xml
SET @xmlDoc = ‘
'42-211'
'42-211- – '
‘
select
a.b.value(‘Old[1]’,’varchar(20)’) as old,
a.b.value(‘New[1]’,’varchar(20)’) as new
from @xmldoc.nodes(‘well’)a(b)
Output:
ColumnName OLDVALUE NEWVALUE
data data data
Please help!
Hello there,
I have a XML column. When I query the table I see the column values as AAEAAAD/////AQAAAAA…………….. and so on.
Is there a way I can read the actual XML file stored. How do I get it. I do not know the schema of nodes in the XML to query as given initially in this blog.
Any help is much appreciated.
Thank you,
Suchi