MS SQL XML - OPENXML & ntext

Author

OPENXML & ntext

Nikola Milic

2005-11-01, 9:23 am

Hi,
I'm trying to import big xml file (cannot fit into varchar) by OPENXML. I
need ntext local variable to input xml text to sp_xml_preparedocume
nt. But
it is not possible to declare local variable of ntext type. I tried to pass
"pointer" to ntext (see script below) but it doesn't work as well.

How can I do this? I want to use pure T-sql solution, without any of
external COM objects.

I use SS2000, SP4, Win 2000 Advance, SP4.

Thanks in advance
Nikola Milic

CREATE TABLE tempdb.dbo.[testXML] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[textXML] [ntext] COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
CONSTRAINT [PK_testXML] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO tempdb.dbo.testXML (textXML)
SELECT '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>' AS textXML
GO

--READ XML
DECLARE @idoc int
DECLARE @doc varbinary(16) -- ntext -- varchar(8000)

SELECT @doc = TEXTPTR(textXML) -- CONVERT(varchar(8000
), textXML)
FROM tempdb.dbo.testXML

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocume
nt @idoc OUTPUT, @doc

PRINT @idoc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

--CLEAR
EXEC sp_xml_removedocumen
t @idoc
GO

Nikola Milic

2005-11-02, 3:23 am

Hi,
Here is link which can solve this problem.
http://www.awprofessional.com/artic...&seqNum=16&rl=1

Regards
Nikola

"Nikola Milic" <hotmnikola@hotmail.com> wrote in message
news:%235nOs2u3FHA.3868@TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to import big xml file (cannot fit into varchar) by OPENXML. I
> need ntext local variable to input xml text to sp_xml_preparedocume
nt. But
> it is not possible to declare local variable of ntext type. I tried to
> pass "pointer" to ntext (see script below) but it doesn't work as well.
>
> How can I do this? I want to use pure T-sql solution, without any of
> external COM objects.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
>
> Thanks in advance
> Nikola Milic
>
>
>
> CREATE TABLE tempdb.dbo.[testXML] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [textXML] [ntext] COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
> CONSTRAINT [PK_testXML] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO tempdb.dbo.testXML (textXML)
> SELECT '
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail ProductID="11" Quantity="12"/>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>' AS textXML
> GO
>
>
> --READ XML
> DECLARE @idoc int
> DECLARE @doc varbinary(16) -- ntext -- varchar(8000)
>
> SELECT @doc = TEXTPTR(textXML) -- CONVERT(varchar(8000
), textXML)
> FROM tempdb.dbo.testXML
>
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocume
nt @idoc OUTPUT, @doc
>
> PRINT @idoc
>
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
>
> --CLEAR
> EXEC sp_xml_removedocumen
t @idoc
> GO
>
>

Sponsored Links