March 18, 2003 at 2:24 am
I have a Temp table like this. Candidate_Portions (Candidate_ID int Portion_ID int, Portion_XML Text). Now I have to retreive the data from this table and parse the XML.
It's not allwoing me to Fetch or assign Text datatype variable into any local variable (Text). Can I do WRITETEXT onto a Text variable from a Table column?
March 21, 2003 at 8:00 am
This was removed by the editor as SPAM
March 26, 2003 at 4:21 am
Unfortunately you cannot use Text as a local verible type (only in SPs as input/output but not internal variables).
April 3, 2003 at 8:40 am
quote:
I have a Temp table like this. Candidate_Portions (Candidate_ID int Portion_ID int, Portion_XML Text). Now I have to retreive the data from this table and parse the XML.It's not allwoing me to Fetch or assign Text datatype variable into any local variable (Text). Can I do WRITETEXT onto a Text variable from a Table column?
I found a way to put the text into (potentially high number of) varchar(7500) variables within the stored procedure, then reference them all through a pointer. Let me know if you'd like to see it.
Rick Todd
April 3, 2003 at 10:15 am
Please post example.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 10:27 am
I figured I would have been better off just posting the example in the first place, since I knew people would want it...
In the following sample, XMLPost is a table with 3 columns, XMLPostKey, PostStatusCode, and XMLString which is the Text column.
<sample>
DECLARE @i_XMLHandleint
DECLARE @i_XMLString1 varchar(7500), @i_XMLString2 varchar(7500), @i_XMLString3 varchar(7500), @i_XMLString4 varchar(7500),
@i_XMLString5 varchar(7500), @i_XMLString6 varchar(7500), @i_XMLString7 varchar(7500), @i_XMLString8 varchar(7500),
@i_XMLString9 varchar(7500), @i_XMLString10 varchar(7500), @i_XMLString11 varchar(7500), @i_XMLString12 varchar(7500),
@i_XMLString13 varchar(7500), @i_XMLString14 varchar(7500), @i_XMLString15 varchar(7500), @i_XMLString16 varchar(7500),
@i_XMLString17 varchar(7500), @i_XMLString18 varchar(7500), @i_XMLString19 varchar(7500), @i_XMLString20 varchar(7500),
@i_XMLString21 varchar(7500), @i_XMLString22 varchar(7500), @i_XMLString23 varchar(7500), @i_XMLString24 varchar(7500),
@i_XMLString25 varchar(7500), @i_XMLString26 varchar(7500), @i_XMLString27 varchar(7500), @i_XMLString28 varchar(7500),
@i_XMLString29 varchar(7500), @i_XMLString30 varchar(7500), @i_XMLString31 varchar(7500), @i_XMLString32 varchar(7500),
@i_XMLString33 varchar(7500), @i_XMLString34 varchar(7500), @i_XMLString35 varchar(7500), @i_XMLString36 varchar(7500),
@i_XMLString37 varchar(7500), @i_XMLString38 varchar(7500), @i_XMLString39 varchar(7500), @i_XMLString40 varchar(7500),
@i_XMLString41 varchar(7500), @i_XMLString42 varchar(7500), @i_XMLString43 varchar(7500), @i_XMLString44 varchar(7500),
@i_XMLString45 varchar(7500), @i_XMLString46 varchar(7500), @i_XMLString47 varchar(7500), @i_XMLString48 varchar(7500),
@i_XMLString49 varchar(7500), @i_XMLString50 varchar(7500), @i_XMLString51 varchar(7500), @i_XMLString52 varchar(7500),
@i_XMLString53 varchar(7500), @i_XMLString54 varchar(7500), @i_XMLString55 varchar(7500), @i_XMLString56 varchar(7500),
@i_XMLString57 varchar(7500), @i_XMLString58 varchar(7500), @i_XMLString59 varchar(7500), @i_XMLString60 varchar(7500),
@i_XMLString61 varchar(7500), @i_XMLString62 varchar(7500), @i_XMLString63 varchar(7500), @i_XMLString64 varchar(7500),
@i_XMLString65 varchar(7500), @i_XMLString66 varchar(7500), @i_XMLString67 varchar(7500), @i_XMLString68 varchar(7500),
@i_XMLString69 varchar(7500), @i_XMLString70 varchar(7500), @i_XMLString71 varchar(7500), @i_XMLString72 varchar(7500),
@i_XMLString73 varchar(7500), @i_XMLString74 varchar(7500), @i_XMLString75 varchar(7500), @i_XMLString76 varchar(7500),
@i_XMLString77 varchar(7500), @i_XMLString78 varchar(7500), @i_XMLString79 varchar(7500), @i_XMLString80 varchar(7500),
@i_XMLString81 varchar(7500), @i_XMLString82 varchar(7500), @i_XMLString83 varchar(7500), @i_XMLString84 varchar(7500),
@i_XMLString85 varchar(7500), @i_XMLString86 varchar(7500), @i_XMLString87 varchar(7500), @i_XMLString88 varchar(7500),
@i_XMLString89 varchar(7500), @i_XMLString90 varchar(7500), @i_XMLString91 varchar(7500), @i_XMLString92 varchar(7500),
@i_XMLString93 varchar(7500), @i_XMLString94 varchar(7500), @i_XMLString95 varchar(7500), @i_XMLString96 varchar(7500),
@i_XMLString97 varchar(7500), @i_XMLString98 varchar(7500), @i_XMLString99 varchar(7500), @i_XMLString100 varchar(7500)
-- Strip the text value into several varchar strings:
SELECT @i_XMLString1 = REPLACE(SUBSTRING( XMLString, 0*7500 + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString2 = REPLACE(SUBSTRING( XMLString, (1*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString3 = REPLACE(SUBSTRING( XMLString, (2*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString4 = REPLACE(SUBSTRING( XMLString, (3*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString5 = REPLACE(SUBSTRING( XMLString, (4*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString6 = REPLACE(SUBSTRING( XMLString, (5*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString7 = REPLACE(SUBSTRING( XMLString, (6*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString8 = REPLACE(SUBSTRING( XMLString, (7*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString9 = REPLACE(SUBSTRING( XMLString, (8*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString10 = REPLACE(SUBSTRING( XMLString, (9*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString11 = REPLACE(SUBSTRING( XMLString, (10*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString12 = REPLACE(SUBSTRING( XMLString, (11*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString13 = REPLACE(SUBSTRING( XMLString, (12*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString14 = REPLACE(SUBSTRING( XMLString, (13*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString15 = REPLACE(SUBSTRING( XMLString, (14*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString16 = REPLACE(SUBSTRING( XMLString, (15*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString17 = REPLACE(SUBSTRING( XMLString, (16*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString18 = REPLACE(SUBSTRING( XMLString, (17*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString19 = REPLACE(SUBSTRING( XMLString, (18*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString20 = REPLACE(SUBSTRING( XMLString, (19*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString21 = REPLACE(SUBSTRING( XMLString, (20*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString22 = REPLACE(SUBSTRING( XMLString, (21*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString23 = REPLACE(SUBSTRING( XMLString, (22*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString24 = REPLACE(SUBSTRING( XMLString, (23*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString25 = REPLACE(SUBSTRING( XMLString, (24*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString26 = REPLACE(SUBSTRING( XMLString, (25*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString27 = REPLACE(SUBSTRING( XMLString, (26*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString28 = REPLACE(SUBSTRING( XMLString, (27*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString29 = REPLACE(SUBSTRING( XMLString, (28*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString30 = REPLACE(SUBSTRING( XMLString, (29*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString31 = REPLACE(SUBSTRING( XMLString, (30*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString32 = REPLACE(SUBSTRING( XMLString, (31*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString33 = REPLACE(SUBSTRING( XMLString, (32*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString34 = REPLACE(SUBSTRING( XMLString, (33*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString35 = REPLACE(SUBSTRING( XMLString, (34*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString36 = REPLACE(SUBSTRING( XMLString, (35*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString37 = REPLACE(SUBSTRING( XMLString, (36*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString38 = REPLACE(SUBSTRING( XMLString, (37*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString39 = REPLACE(SUBSTRING( XMLString, (38*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString40 = REPLACE(SUBSTRING( XMLString, (39*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString41 = REPLACE(SUBSTRING( XMLString, (40*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString42 = REPLACE(SUBSTRING( XMLString, (41*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString43 = REPLACE(SUBSTRING( XMLString, (42*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString44 = REPLACE(SUBSTRING( XMLString, (43*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString45 = REPLACE(SUBSTRING( XMLString, (44*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString46 = REPLACE(SUBSTRING( XMLString, (45*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString47 = REPLACE(SUBSTRING( XMLString, (46*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString48 = REPLACE(SUBSTRING( XMLString, (47*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString49 = REPLACE(SUBSTRING( XMLString, (48*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString50 = REPLACE(SUBSTRING( XMLString, (49*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString51 = REPLACE(SUBSTRING( XMLString, (50*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString52 = REPLACE(SUBSTRING( XMLString, (51*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString53 = REPLACE(SUBSTRING( XMLString, (52*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString54 = REPLACE(SUBSTRING( XMLString, (53*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString55 = REPLACE(SUBSTRING( XMLString, (54*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString56 = REPLACE(SUBSTRING( XMLString, (55*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString57 = REPLACE(SUBSTRING( XMLString, (56*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString58 = REPLACE(SUBSTRING( XMLString, (57*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString59 = REPLACE(SUBSTRING( XMLString, (58*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString60 = REPLACE(SUBSTRING( XMLString, (59*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString61 = REPLACE(SUBSTRING( XMLString, (60*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString62 = REPLACE(SUBSTRING( XMLString, (61*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString63 = REPLACE(SUBSTRING( XMLString, (62*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString64 = REPLACE(SUBSTRING( XMLString, (63*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString65 = REPLACE(SUBSTRING( XMLString, (64*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString66 = REPLACE(SUBSTRING( XMLString, (65*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString67 = REPLACE(SUBSTRING( XMLString, (66*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString68 = REPLACE(SUBSTRING( XMLString, (67*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString69 = REPLACE(SUBSTRING( XMLString, (68*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString70 = REPLACE(SUBSTRING( XMLString, (69*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString71 = REPLACE(SUBSTRING( XMLString, (70*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString72 = REPLACE(SUBSTRING( XMLString, (71*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString73 = REPLACE(SUBSTRING( XMLString, (72*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString74 = REPLACE(SUBSTRING( XMLString, (73*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString75 = REPLACE(SUBSTRING( XMLString, (74*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString76 = REPLACE(SUBSTRING( XMLString, (75*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString77 = REPLACE(SUBSTRING( XMLString, (76*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString78 = REPLACE(SUBSTRING( XMLString, (77*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString79 = REPLACE(SUBSTRING( XMLString, (78*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString80 = REPLACE(SUBSTRING( XMLString, (79*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString81 = REPLACE(SUBSTRING( XMLString, (80*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString82 = REPLACE(SUBSTRING( XMLString, (81*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString83 = REPLACE(SUBSTRING( XMLString, (82*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString84 = REPLACE(SUBSTRING( XMLString, (83*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString85 = REPLACE(SUBSTRING( XMLString, (84*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString86 = REPLACE(SUBSTRING( XMLString, (85*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString87 = REPLACE(SUBSTRING( XMLString, (86*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString88 = REPLACE(SUBSTRING( XMLString, (87*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString89 = REPLACE(SUBSTRING( XMLString, (88*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString90 = REPLACE(SUBSTRING( XMLString, (89*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString91 = REPLACE(SUBSTRING( XMLString, (90*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString92 = REPLACE(SUBSTRING( XMLString, (91*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString93 = REPLACE(SUBSTRING( XMLString, (92*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString94 = REPLACE(SUBSTRING( XMLString, (93*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString95 = REPLACE(SUBSTRING( XMLString, (94*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString96 = REPLACE(SUBSTRING( XMLString, (95*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString97 = REPLACE(SUBSTRING( XMLString, (96*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString98 = REPLACE(SUBSTRING( XMLString, (97*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ),
@i_XMLString99 = REPLACE(SUBSTRING( XMLString, (98*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) ), @i_XMLString100 = REPLACE(SUBSTRING( XMLString, (99*7500 ) + 1, 7500 ), CHAR(39), CHAR(39)+CHAR(39) )
FROM dbo.XMLPost
WHERE XMLPostKey = @i_XMLPostKey
-- Use dynamic SQL to build the prepare doc call:
EXEC(
'DECLARE @Handle int
EXEC sp_xml_preparedocument @Handle OUT, ''' +
@i_XMLString1 + @i_XMLString2 + @i_XMLString3 + @i_XMLString4 +
@i_XMLString5 + @i_XMLString6 + @i_XMLString7 + @i_XMLString8 +
@i_XMLString9 + @i_XMLString10 + @i_XMLString11 + @i_XMLString12 +
@i_XMLString13 + @i_XMLString14 + @i_XMLString15 + @i_XMLString16 +
@i_XMLString17 + @i_XMLString18 + @i_XMLString19 + @i_XMLString20 +
@i_XMLString21 + @i_XMLString22 + @i_XMLString23 + @i_XMLString24 +
@i_XMLString25 + @i_XMLString26 + @i_XMLString27 + @i_XMLString28 +
@i_XMLString29 + @i_XMLString30 + @i_XMLString31 + @i_XMLString32 +
@i_XMLString33 + @i_XMLString34 + @i_XMLString35 + @i_XMLString36 +
@i_XMLString37 + @i_XMLString38 + @i_XMLString39 + @i_XMLString40 +
@i_XMLString41 + @i_XMLString42 + @i_XMLString43 + @i_XMLString44 +
@i_XMLString45 + @i_XMLString46 + @i_XMLString47 + @i_XMLString48 +
@i_XMLString49 + @i_XMLString50 + @i_XMLString51 + @i_XMLString52 +
@i_XMLString53 + @i_XMLString54 + @i_XMLString55 + @i_XMLString56 +
@i_XMLString57 + @i_XMLString58 + @i_XMLString59 + @i_XMLString60 +
@i_XMLString61 + @i_XMLString62 + @i_XMLString63 + @i_XMLString64 +
@i_XMLString65 + @i_XMLString66 + @i_XMLString67 + @i_XMLString68 +
@i_XMLString69 + @i_XMLString70 + @i_XMLString71 + @i_XMLString72 +
@i_XMLString73 + @i_XMLString74 + @i_XMLString75 + @i_XMLString76 +
@i_XMLString77 + @i_XMLString78 + @i_XMLString79 + @i_XMLString80 +
@i_XMLString81 + @i_XMLString82 + @i_XMLString83 + @i_XMLString84 +
@i_XMLString85 + @i_XMLString86 + @i_XMLString87 + @i_XMLString88 +
@i_XMLString89 + @i_XMLString90 + @i_XMLString91 + @i_XMLString92 +
@i_XMLString93 + @i_XMLString94 + @i_XMLString95 + @i_XMLString96 +
@i_XMLString97 + @i_XMLString98 + @i_XMLString99 + @i_XMLString100 + '''
DECLARE i_XMLCursor CURSOR FOR SELECT @Handle'
)
OPEN i_XMLCursor
FETCH i_XMLCursor into @i_XMLHandle
DEALLOCATE i_XMLCursor
IF @i_XMLHandle IS NULL
RAISERROR( 'Invalid Handle!', 16, 1 )
-- now you can just refer to the XMLObject just like you would normally
SELECT *
FROM OPENXML(@XMLHandle, <XPath Query>, 3)
</sample>
I must say I feel bad I can't credit the website where I got the basis for this sample code. If anyone knows where it came from, please say so here.
A minor tip: consider enabling the "Text In Row" option for the table if there's a chance the value in the Text field might be small enough to put it right in the row, so that SQL Server doesn't have to use the pointer to find the info.
Hope this helps,
Rick
Rick Todd
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply