Import data from XML variable into SQL Server 2008 r2 table

Posted on 2014-10-03
Medium Priority
Last Modified: 2015-03-09
hI ,

im looking for Sql statement to import data from XML variable and dump into a table.
I only need data fro within the <Pption></Option> tags below.
CREATE TABLE [dbo].[tblUserMst](
      [UserID] [int] IDENTITY(1,1) NOT NULL,
      [UserName] [varchar](500) NULL)
declare @input xml = '<OPTION value=bk12_info.shtml>ALEX</OPTION>
 <OPTION value=bk11_info.shtml>JOHN</OPTION>'
-------------OutPut -------
UserID     UserName
1               Alex
2               John

Question by:kishan66
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 26

Expert Comment

by:Shaun Kline
ID: 40359589
Something like this?
declare @input xml 
SET @input = '<OPTION value="bk12_info.shtml">ALEX</OPTION><OPTION value="bk11_info.shtml">JOHN</OPTION>'

SELECT ROW_NUMBER() OVER(ORDER BY x.value('.', 'varchar(50)')) AS ID, x.value('.', 'varchar(50)') AS Name
FROM @input.nodes('//OPTION') AS data(x)

Open in new window

LVL 24

Expert Comment

by:Phillip Burton
ID: 40359636
insert into dbo.tblUserMst(UserName)
select Rows.n.value('.','nvarchar(100)')
from @input.nodes('/OPTION') Rows(n)

select * from dbo.tblUserMst

Open in new window


Author Comment

ID: 40360512
Guys (Shaun, Phillip)

Both of your code works only if i have one Option node  like...
<Option  value="bk12_info.shtml>one</Option>.  [Works]

if there are multiple like
<Option  value="bk12_info.shtml>one</Option>
<Option  value="bk13_info.shtml>two</Option>

It throws error "XML parsing: line 1, character 50, A string literal was expected".
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 26

Expert Comment

by:Shaun Kline
ID: 40360722
The value property needs to be enclosed in quotation marks, as it is in my example. The example you show only has the quotation mark on one side of the value property.

Author Comment

ID: 40362339
Shaun, Thanks for the the reply.
But in reality the xml i receive doesn't have value enclosed in quotation marks.  More over i donot need that value either.
LVL 24

Accepted Solution

Phillip Burton earned 1500 total points
ID: 40363141
I don't really understand your comment.

If you use this code:

truncate table dbo.tblUserMst
declare @input xml = '<Option value="bk12_info.shtml">one</Option><Option value="bk13_info.shtml">two</Option>'
insert into dbo.tblUserMst(UserName)
select Rows.n.value('.','nvarchar(100)')
from @input.nodes('/Option') Rows(n)
select * from dbo.tblUserMst

Open in new window

You will get this answer

UserID   UserName
1             one
2             two

Do you not get the same result?
LVL 26

Expert Comment

by:Shaun Kline
ID: 40363542
If your data is not coming with the properties of your XML node enclosed in quotation marks, then the XML is not well-formed and attempts to use it as XML will fail, unless you transform it into well-formed XML.

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question