Manipulate ntext field with XML data in it

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

SQL Server 2000 Forums

Transact-SQL (2000)

Manipulate ntext field with XML data in it

Author

Topic

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-02-17 : 11:18:04

I just received a table that has a ntext data type column that has xml data in it. I'd like to pull this data and separate the xml values into separate columns. Does anyone know how to do this type of thing?

Here is an example xml text that is being held in the field. I removed the real text because of confidentiality but this would show what I'm dealing with.

<articledata><section id="0"><content><![CDATA[Title Text]]></content></section><section id="1"><content><![CDATA[<P>1. One Data.<BR>2. Two Data.<BR><BR>More Data.<BR><BR>And More Data:</P>
<P>1. One Data<BR><BR>2. Two Data.<BR><BR>3. Three Data.</P>
<P>4. Four Data.</P>
<P>5. Five Data.<BR></P>]]></content></section><section id="2"><content><![CDATA[]]></content></section></articledata>

Thanks!
James Hunt

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 12:52:41

<OneOpinion>

Text columns are a pain to deal with from within sql server and XML is a pain to deal with from within sql server.

So if it were me, I'd consider a .net solution to load the ntext value into a xml DOM and then call insert SPs to parse the content to tables.

</OneOpinion>

Be One with the Optimizer
TG

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-02-21 : 11:36:25

Well, I need a way of extracting the data from the xml in the ntext field and putting the data in separate fields. I'm using coldfusion for my web interface and sql server on my backend. I'm pulling this data from a different system and it needs to put it in a different format for our system. Basically instead of keeping the different field values in the separate xml tags, I want to keep the different field values in different db columns. I don't know how to extract the xml data and then extract the column values to separate columns.

Please help if you know how to do something like this.

Thanks!
James

James Hunt

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-03-06 : 10:12:57

This problem hasn't been resolved.

Bump

James Hunt

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 11:05:01

[code]
go
create proc test
@doc ntext = ''
as
DECLARE @idoc int
--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))
go

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>'

exec test @doc

go
drop proc test
[/code]

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources