Reading xml data from a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reading xml data from a string

Author

Topic

krittim
Starting Member

6 Posts

Posted - 2006-07-27 : 07:52:13

Hi,

I want to read xml from a string and save it in SQL. Can anyone help me plz.

Regards,

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-27 : 12:51:39

quote:
read xml from a string

What do you mean by 'a string'? Where is the string currently?

quote:
save it in SQL

How do you want to save it? As string data in a varchar/text column? As normalized data in 1/many tables/columns?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-27 : 14:17:49

[code]
USE Northwind
GO

CREATE TABLE myTable99(fname char(20), lname char(30), phone char(20))
GO

DECLARE @xml varchar(8000), @data varchar(8000), @tag varchar(255), @sql varchar(8000)
, @fname char(20), @lname char(30), @phone char(20)
SET @xml = '<fname>Brett</fname><lname>Kaiser</lname><phone>555-1234</phone>'
SELECT @tag = '', @data = ''

WHILE LEN(@xml) <> LEN(@tag+@data+@tag)+3
BEGIN
SELECT @tag=SUBSTRING(@xml,CHARINDEX('<',@xml), CHARINDEX('>',@xml))
SELECT @data = SUBSTRING(@xml,LEN(@tag)+1,CHARINDEX('<',@xml,LEN(@tag))-LEN(@tag)-1)

IF @tag = '<fname>'
SELECT @fname = @data
ELSE IF @tag = '<lname>'
SELECT @lname = @data
ELSE IF @tag = '<phone>'
SELECT @phone = @data

SELECT @xml = SUBSTRING(@xml,LEN(@tag+@data+@tag)+2,LEN(@xml)-LEN(@tag+@data+@tag)+2)

SELECT @xml,@fname,@data,@tag,LEN(@xml),LEN(@tag+@data+@tag)+2
END

SELECT @tag=SUBSTRING(@xml,CHARINDEX('<',@xml), CHARINDEX('>',@xml))
SELECT @data = SUBSTRING(@xml,LEN(@tag)+1,CHARINDEX('<',@xml,LEN(@tag))-LEN(@tag)-1)

IF @tag = '<fname>'
SELECT @fname = @data
ELSE IF @tag = '<lname>'
SELECT @lname = @data
ELSE IF @tag = '<phone>'
SELECT @phone = @data

SELECT @sql = 'INSERT INTO myTable99(fname,lname,phone) '
+ 'SELECT ''' + RTRIM(@fname) + ''''
+ ',' + '''' + RTRIM(@lname) + ''''
+ ',' + '''' + RTRIM(@phone) + ''''
SELECT @sql

EXEC(@sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-27 : 15:28:17

Hold the phone...I blogged this, and it was pointed out to me to use OPENXML..so I looked it up in BOL and I was floored....

The following code snipet is right out of BOL:


DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-28 : 05:15:45

LOL - I should've pointed that out when I read your first post, but I was waiting for my answers

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-28 : 10:21:36

Well, at least it was a fun exercise in futility...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

krittim
Starting Member

6 Posts

Posted - 2006-07-29 : 00:12:26

thank you guys :)

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-29 : 00:28:32

wow I'm surprised you've never used OPENXML before

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources