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

« SQL SERVER – Simple Example of Creating XML File Using T-SQL

SQL SERVER – Restore Master Database – An Easy Solution »

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

February 13, 2009 by pinaldave

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 (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL XML | 81 Comments

81 Responses

on February 14, 2009 at 5:57 pm | Reply SQL SERVER - Simple Example of Creating XML File Using T-SQL Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Simple Example of Reading XML File Using T-SQL [...]
- on June 9, 2011 at 12:57 pm | Reply Mahboob
  
  Thanks Pinal , really a nice solution, but i have an other issue that i have a large data and want to create its xml file but when i excutes above ur mentioned query i get the a small piece of results(approx 2k characters), please can you halp me out
  Thanks
- on July 29, 2011 at 8:41 pm | Reply Sabeer
  
  Hi Sir,
  
  I have XML that is in the Format
  
  contractTypeId
  15
  
  endDate
  J
  
  startDate
  03/01/2004
  
  restrictedService
  False
  
  requestType
  R
  
  requestTypeId
  4
  
  contractAmount
  $10.00
  
  versionNo
  1
  
  listId
  249
  
  changeOrderId
  -1
  
  supplementNo
  0
  
  templateId
  19
  
  requestDocId
  224
  
  requestId
  234
  
  orgUnitTemplateId
  19
  
  contractTypeRequired
  False
  
  EndDate
  31/03/2011
  
  it’s required for me to update the Only startdate and EndDate values with new dates. Please help me on this ?
- on August 31, 2011 at 2:18 pm | Reply dinesh singu
  
  sir I need help for inserting muliple rows in table
  for 100 colours then I have to folowing
  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]/Color1[100]‘,‘varchar(10)’) AS Color1,
  this much code we have.we have any option to write in 2 or 3 lines
  pls help me .mail me on [email removed]
on February 16, 2009 at 8:03 am | Reply Puneet

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 ‘‘’.
- on August 31, 2011 at 2:12 pm | Reply dinesh singu
  
  hello give proper single quotes
on February 16, 2009 at 10:33 am | Reply Munish Bansal

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
on February 16, 2009 at 1:51 pm | Reply smita

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
- on December 6, 2011 at 9:03 am | Reply Akhil
  
  DECLARE @productIds xml
  SET @productIds =’3615′
  
  SELECT
  ParamValues.ID.value(‘.’,'VARCHAR(20)’)
  FROM @productIds.nodes(‘/Products/id’) as ParamValues(ID)
on February 17, 2009 at 7:20 am | Reply zod

>>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.
on February 17, 2009 at 7:43 am | Reply zod

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);
on February 22, 2009 at 4:20 pm | Reply Yousuf

Hi Pinal,

How can i parse XML,which is send as a input parameter into stored procedure.
on July 7, 2009 at 6:30 pm | Reply sinan

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 …
on July 27, 2009 at 10:47 am | Reply Shital Kasliwal

hello sir,

can i load table attibute with its value in xml file using sql.
if yes pls give me example query.
on August 4, 2009 at 12:17 pm | Reply Shital Kasliwal

Hello Sir,
how can i load xml file in perticular table or different tables.
pls help us.
on August 6, 2009 at 7:57 pm | Reply A.K. Philip

Good one.
on August 7, 2009 at 12:28 am | Reply A.K.Philip

Mr. Pinal,
How can I find out the count if there is more than one parents?

Thanks
~Philip
on August 19, 2009 at 2:06 am | Reply sjothi

I need to get the value of {{BOLID}}

Thanks again,
Sjothi
on September 8, 2009 at 3:47 am | Reply Benn

Hi,

I would like to read an xml and get a value from it but the xml file is returned via the following url:

http://maps.google.com/maps/geo?q=MyPostalCode&output=xml&key=MyGoogleMapAPI_Key

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?
on September 8, 2009 at 10:15 am | Reply Tejas Shah

@Benn,

If you are using SQL 2005 and above version, then you can get values by this query:

DECLARE @XML XML
SET @XML = ‘

MyPostalCode

602
geocode

‘

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
on September 9, 2009 at 9:27 am | Reply Benn

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.
on September 9, 2009 at 11:50 am | Reply Tejas Shah

@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
on September 9, 2009 at 11:40 pm | Reply Benn

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
on February 16, 2010 at 3:11 pm | Reply chandrakant Singh

Hi Pinal Dave

help me Out to become Best Sql DBA

Thanks & Regards
Chandrakant Singh
on March 10, 2010 at 9:32 am | Reply Cheny

Please provide sample code to use bcp export data from table/view to xml file.

Thanks.
on March 11, 2010 at 2:33 am | Reply BSK

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)
on March 11, 2010 at 9:30 pm | Reply Einar

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).
on March 24, 2010 at 12:11 pm | Reply TAPAN KUMAR

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();
}
on August 6, 2010 at 10:39 am | Reply vinoth

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
on August 6, 2010 at 11:40 am | Reply vinoth

pls urgent
on August 6, 2010 at 4:08 pm | Reply giri

hello sir ,

what will be the select query if we have ‘N’ number of colors tag ….. plz reply thank you
on August 9, 2010 at 11:42 am | Reply vinothraja

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
on August 20, 2010 at 1:35 am | Reply Jason Springs

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
on November 1, 2010 at 7:32 pm | Reply kinjal

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
on November 22, 2010 at 11:55 am | Reply Ganesh

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.
on December 8, 2010 at 4:52 pm | Reply sunil

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
on December 15, 2010 at 2:58 am | Reply Krishusavalia

–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
on December 30, 2010 at 10:59 am | Reply Ram

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
on December 30, 2010 at 5:44 pm | Reply Niladri

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
on December 30, 2010 at 6:08 pm | Reply Daivagna

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
on January 7, 2011 at 1:30 am | Reply Pinky

Sorry, the xml did not get pasted properly earlier

HEADER1

HEADER2

HEADER3

HEADER4

rank1
rank2
Value1
rank3
Value2
rank4
Value3
on February 3, 2011 at 4:36 pm | Reply VENGATESH

hi
on February 3, 2011 at 4:38 pm | Reply VENGATESH

hi just remove that ` till symbol and put ‘ single quetation it will work thanks
on March 9, 2011 at 2:51 am | Reply william

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
on March 9, 2011 at 3:43 am | Reply William

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!
on March 10, 2011 at 5:29 pm | Reply Suchi

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
on March 23, 2011 at 7:02 pm | Reply narasimha

excellent
on March 23, 2011 at 7:06 pm | Reply narasimha

hi William,

you should assign the value in xml format.
on March 31, 2011 at 7:50 pm | Reply kumar

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
- on April 1, 2011 at 10:56 am | Reply madhivanan
  
  Post the code that you have used
on April 2, 2011 at 5:59 am | Reply Dan Q

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.
on April 2, 2011 at 6:11 am | Reply Dan Q

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
on April 16, 2011 at 11:29 am | Reply Manikandan S

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
on April 24, 2011 at 8:17 pm | Reply Mani

– 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);
on May 5, 2011 at 4:20 pm | Reply srinivasan

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
on May 5, 2011 at 4:22 pm | Reply srinivasan

set @xml_ =’false100504測試合同 ÓPCM üñ ¿¡’
on May 23, 2011 at 10:57 am | Reply Sanjay

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
on May 23, 2011 at 3:55 pm | Reply rahul

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
on May 23, 2011 at 3:56 pm | Reply rahul

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
on June 28, 2011 at 1:49 am | Reply Vinod

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.
on June 28, 2011 at 1:55 am | Reply Vinod

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
on July 20, 2011 at 3:37 am | Reply John Jacobson

Thanks so much for your clear concise examples!! You are a life saver.
on August 10, 2011 at 1:54 pm | Reply sharon

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
on August 10, 2011 at 1:57 pm | Reply sharon

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
on September 9, 2011 at 11:39 pm | Reply dann

hi, pueden ayudarme se los voy a agradecer mucho.

how create schema for this xml?

2345345
345
04/25/2011
09:12 am
on October 6, 2011 at 7:09 pm | Reply John Effle

Thank you Mr. Pinal
on October 10, 2011 at 9:22 pm | Reply S.L.

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??
on November 22, 2011 at 8:35 pm | Reply penolepe

you are perfect. very thanks.
on January 20, 2012 at 11:15 pm | Reply vasu

Sir

Help me in getting the text of a particular value attribute
on January 21, 2012 at 7:01 am | Reply SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31 « SQL Server Journey with SQL Authority

[...] SELECT * FROM XML Shredding XML Validate an XML document in TSQL using XSD Simple Example of Reading XML File Using T-SQL Simple Example of Creating XML File Using [...]
on February 10, 2012 at 11:21 am | Reply ram

Dear

i have one employee table in xml
how to display the all records from xml
like(select *from emp)
on February 17, 2012 at 11:53 am | Reply praveen kumar

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.
on April 12, 2012 at 12:17 pm | Reply ramdas

“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.
- on May 3, 2012 at 5:39 pm | Reply Azziet
  
  Use This
  SELECT xCol.value(‘(//author/last-name)[1]‘, ‘nvarchar(50)’) LastName
  FROM docs
on June 11, 2012 at 10:27 pm | Reply Dave

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):
on June 11, 2012 at 11:13 pm | Reply Dave

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
on June 12, 2012 at 10:10 pm | Reply sobhan

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)
on June 21, 2012 at 5:47 pm | Reply Dhinakaran

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
on August 24, 2012 at 3:21 am | Reply Mike

How can I do if the XML file has namespace?
on November 25, 2012 at 9:24 am | Reply bharatnet

Hello sir,

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

Thank You,
bharat
on February 16, 2013 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

[...] Simple Example of Reading XML File Using T-SQL [...]