SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reading xml data from a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

krittim
Starting Member

Bangladesh
6 Posts

Posted - 07/27/2006 :  07:52:13  Show Profile  Click to see krittim's MSN Messenger address  Reply with Quote
Hi,

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

Regards,

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/27/2006 :  12:51:39  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/27/2006 :  14:17:49  Show Profile  Reply with Quote

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
 



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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/27/2006 :  15:28:17  Show Profile  Reply with Quote
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
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/28/2006 :  05:15:45  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/28/2006 :  10:21:36  Show Profile  Reply with Quote
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
Go to Top of Page

krittim
Starting Member

Bangladesh
6 Posts

Posted - 07/29/2006 :  00:12:26  Show Profile  Click to see krittim's MSN Messenger address  Reply with Quote
thank you guys :)
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 07/29/2006 :  00:28:32  Show Profile  Reply with Quote
wow I'm surprised you've never used OPENXML before
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000