xml text in temporary table to SP in MSSQL 2000

Question

xml text in temporary table to SP in MSSQL 2000

gan-685682

Ten Centuries

Points: 1216
More actions
May 23, 2008 at 3:14 am

#182708

let's say I have this one, no problem:
CREATE PROCEDURE au_info
@var text
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@var
PRINT CAST(@var as varchar(8000))
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/base/Customers')
-- WITH Customers
GO
[Code]
EXEC au_info @var = '
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
'
[/code]
but what if I only have the xml text in a temporary table:
[Code]
create table #t1 (col1 text)
insert into #t1
Values('
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
')
[/code]
if i do this:
[Code]
EXEC au_info @var = (Select col1 from #t1)
[/code]
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
[Code]
EXEC au_info (Select col1 from #t1)
[/code]
Server: Msg 201, Level 16, State 3, Procedure au_info, Line 0
Procedure 'au_info' expects parameter '@var', which was not supplied.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gan-685682 Ten Centuries Points: 1216 More actions · Answer 1

[Code]
create table #t1 (col1 text)
insert into #t1
Values('
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
')
[/code]

should be :

<ROOT&gt

<Customers CustomerID="XYZAA" ContactName="Joe"

CompanyName="Company1">

<Orders CustomerID="XYZAA"

OrderDate="2000-08-25T00:00:00"/>

<Orders CustomerID="XYZAA"

OrderDate="2000-10-03T00:00:00"/>

<Customers CustomerID="XYZBB" ContactName="Steve"

CompanyName="Company2">No Orders yet!

</ROOT&gt

and I do not wish for data loss, using varchar/nvarchar

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]

Select @var = col1 from #t1

EXEC au_info @var

[/font]

MH-09-AM-8694

gan-685682 Ten Centuries Points: 1216 More actions · Answer 3

Mahesh Bote (5/23/2008)
[font="Verdana"]
Select @var = col1 from #t1
EXEC au_info @var
[/font]

where's declaration of @var?

Do not tell me:

DECLARE @var text

or

DECLARE @var nvarchar(4000)

😀