Solved

Import data from XML variable into SQL Server 2008 r2 table

Posted on 2014-10-03
7
101 Views
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
-------------------------------

Thanks
0
Comment
Question by:kishan66
  • 3
  • 2
  • 2
7 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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

0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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

0
 

Author Comment

by:kishan66
Comment Utility
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".
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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.
0
 

Author Comment

by:kishan66
Comment Utility
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.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
I don't really understand your comment.

If you use this code:

truncate table dbo.tblUserMst
go
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?
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now