sql server 中XML太长 提取时数据丢失 解决？？？？

[songguozhi]

create proc xxxx
	@doc ntext=NULL
as 
begin
declare @idoc int
--declare @pre table(t ntext)
--declare @doc varchar(8000)
set @doc='
<upd:Update xmlns:pub="http://schemas.microsoft.com/msus/2002/12/Publishing" 
xmlns:msp="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/WindowsInstaller" 
xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules" 
xmlns:mspblob="http://www.microsoft.com/msi/patch_applicability.xsd" 
xmlns:upd="http://schemas.microsoft.com/msus/2002/12/Update">
	<!--PUID[513870]Name[MSCTF_8022_INTL]-->
	<upd:UpdateIdentity UpdateID="ff096040-4f3c-478d-9a85-03edfd9920fc" RevisionNumber="100"/>
	<upd:Properties UpdateType="Software" DefaultPropertiesLanguage="en" Handler="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/WindowsInstaller" PublicationState="Published" CreationDate="2006-05-05T20:55:13.009Z" PublisherID="9b068e57-8e2d-4e42-b091-74bdb345f120" MaxDownloadSize="1736295" MinDownloadSize="0">
		<upd:InstallationBehavior RebootBehavior="CanRequestReboot"/>
	</upd:Properties>
	<upd:LocalizedPropertiesCollection>
		<upd:LocalizedProperties>
			<upd:Language>en</upd:Language>
			<upd:Title>MSCTF_8022_INTL</upd:Title>
		</upd:LocalizedProperties>
	</upd:LocalizedPropertiesCollection>
	<upd:Relationships>
		<upd:Prerequisites>
			<upd:UpdateIdentity UpdateID="1403f223-a63f-f572-82ba-c92391218055"/>
			<upd:UpdateIdentity UpdateID="3B4B8621-726E-43A6-B43B-37D07EC7019F"/>
		</upd:Prerequisites>
	</upd:Relationships>
	<upd:ApplicabilityRules>
		<upd:IsInstalled>
			<mar:MsiPatchInstalled xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules"/>
		</upd:IsInstalled>
		<upd:IsSuperseded>
			<mar:MsiPatchSuperseded xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules"/>
		</upd:IsSuperseded>
		<upd:IsInstallable>
			<mar:MsiPatchInstallable xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules"/>
		</upd:IsInstallable>
		<upd:Metadata>
			<mar:MsiPatchMetadata xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules">
				<MsiPatch xmlns="http://www.microsoft.com/msi/patch_applicability.xsd" SchemaVersion="1.0.0.0" PatchGUID="{8BE3C22E-85D7-41EB-8D4E-1053DBA17575}" MinMsiVersion="3">
					<TargetProduct MinMsiVersion="100">
						<TargetProductCode Validate="true">{901F040C-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
						<TargetVersion Validate="false" ComparisonType="None" ComparisonFilter="None">11.0.5614.0</TargetVersion>
						<TargetLanguage Validate="false">1036</TargetLanguage>
						<UpdatedLanguages>1036</UpdatedLanguages>
						<UpgradeCode Validate="false">{001F0000-6000-11D3-8CFE-0050048383C9}</UpgradeCode>
					</TargetProduct>
					<TargetProduct MinMsiVersion="100">
						<TargetProductCode Validate="true">{901F0C0A-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
						<TargetVersion Validate="false" ComparisonType="None" ComparisonFilter="None">11.0.5614.1</TargetVersion>
						<TargetLanguage Validate="false">3082</TargetLanguage>
						<UpdatedLanguages>3082</UpdatedLanguages>
						<UpgradeCode Validate="false">{001F0000-6000-11D3-8CFE-0050048383C9}</UpgradeCode>
					</TargetProduct>
					<TargetProductCode>{91A10C0A-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
					<TargetProductCode>{91A10407-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
										<TargetProductCode>{90FD0411-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
					<TargetProductCode>{90E30409-6000-11D3-8CFE-0150048383C9}</TargetProductCode>
										<ObsoletedPatch>{525CE135-97D5-4D3F-A168-BA9E4EADEA0A}</ObsoletedPatch>
					<ObsoletedPatch>{3597A0F7-B55A-4762-BE7A-7E6B70CE577D}</ObsoletedPatch>
					<ObsoletedPatch>{0FE25E55-5C81-418F-9E1E-FDDEE4BCBA9B}</ObsoletedPatch>
					<SequenceData>
						<PatchFamily>msctf</PatchFamily>
						<Sequence>8022</Sequence>
						<Attributes>1</Attributes>
					</SequenceData>
				</MsiPatch>
			</mar:MsiPatchMetadata>
		</upd:Metadata>
	</upd:ApplicabilityRules>
	<upd:Files>
		<upd:File Digest="Kx3h3mp6ep1160kPLBjS+RRJJiU=" DigestAlgorithm="SHA1" FileName="MSCTF.CAB" Size="1736295" Modified="2006-05-03T17:44:00.8400000-07:00" PatchingType="SelfContained"/>
	</upd:Files>
	<upd:HandlerSpecificData xmlns:pub="http://schemas.microsoft.com/msus/2002/12/Publishing" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="msp:WindowsInstaller">
		<msp:MspData xmlns:msp="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/WindowsInstaller" CommandLine="DISABLESRCPROMPT=1 LOCALCACHESRCRES=0 NOLOCALCACHEROLLBACK=1" UninstallCommandLine="DISABLESRCPROMPT=1 LOCALCACHESRCRES=0 NOLOCALCACHEROLLBACK=1" FullFilePatchCode="{8be3c22e-85d7-41eb-8d4e-1053dba17575}"/>
	</upd:HandlerSpecificData>
</upd:Update>
'


EXEC sp_xml_preparedocument @idoc OUTPUT, @doc , '<q xmlns:upd="http://schemas.microsoft.com/msus/2002/12/Update"
	 xmlns:pub="http://schemas.microsoft.com/msus/2002/12/Publishing" 
xmlns:msp="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/WindowsInstaller" 
xmlns:mar="http://schemas.microsoft.com/msus/2002/12/MsiApplicabilityRules" 
xmlns:mspblob="http://www.microsoft.com/msi/patch_applicability.xsd"/>'


SELECT xmltext FROM OPENXML (@idoc, '/upd:Update/upd:ApplicabilityRules')
	WITH(xmltext ntext '@mp:xmltext')

SELECT xmltext FROM  OPENXML (@idoc, '/upd:Update/upd:Files')
 WITH(xmltext ntext '@mp:xmltext')

EXEC sp_xml_removedocument   @idoc
end
go
exec xxxx
go
drop proc xxxx

由于帖子字符数量限制，没有将源文件内容全部贴出来，如果哪位需要，发邮件到[e-mail]，我会在5分钟内将源文件发出来
ApplicabilityRules无法获取，我发现是因为太长，我试着删除一部分，再提取则可以提取成功；
同时我也采用了query方法，但是一样的会丢失，无法获取数据；

哪位有类似问题经验的请赐教一下！！！！

[budong0000]

以前也遇到长字符串，后来就用拼接了， 比如：

declare @s varchar(max)
set @s = '.&*^)#*(LJRJEKHRUIE..........'
set @s += 'lsfjsdiofsdfdfs'
set @s += 'lsfjsdiofsdfdfs'

[--小F--]

不懂XML 帮顶

[htl258_Tony]

create proc xxxx
    @doc varchar(max)=NULL
as 

改为这样呢？

[jwwyqs]

[Quote=引用 2 楼 fredrickhu 的回复:]
不懂XML 帮顶
[/Quote]
up

[songguozhi]

期待啊……………………………………………………

[Garnett_KG]

恐怕不是xml丢失了，而是SELECT 返回的长度有限制.

你可以试试将select的结果存至一个table，然后export到一个excel档，再看看结果对不对.

[songguozhi]

[Quote=引用 7 楼 garnett_kg 的回复:]
恐怕不是xml丢失了，而是SELECT 返回的长度有限制.

你可以试试将select的结果存至一个table，然后export到一个excel档，再看看结果对不对.
[/Quote]

如何解决？？？？？？？？？？？？？？？？？？？？？？？

[--小F--]

2005以上能不能将XML保存到VARCHAR(MAX)字段中去?