EXEC xp_cmdshell 'type C:\config.xml'如果utf-8
则乱码
有没有好的解决方式或者sql2000其他读取xml某个字段的方法xml文件内容示例:
<Config>
<DetectInterval>60</DetectInterval>
<ComPort>1</ComPort>
<ComPort2>6</ComPort2>
<Stop>N</Stop>
<SmsComPort>2</SmsComPort>
<VoiceAlarm>Y</VoiceAlarm>
<AutoDeal>N</AutoDeal>
<AutoOpen>Y</AutoOpen>
<Temperature>0</Temperature>
<IsOpen>Y</IsOpen>
<StartAlarm>Y</StartAlarm>
<DetectIntervalReal>1</DetectIntervalReal>
<AutoState>Y</AutoState>
<AutoStateTime>10</AutoStateTime>
<ControllerTime>30</ControllerTime>
</Config>需要从xml的AutoDeal读取时y还是n,然后触发器做相应操作.
则乱码
有没有好的解决方式或者sql2000其他读取xml某个字段的方法xml文件内容示例:
<Config>
<DetectInterval>60</DetectInterval>
<ComPort>1</ComPort>
<ComPort2>6</ComPort2>
<Stop>N</Stop>
<SmsComPort>2</SmsComPort>
<VoiceAlarm>Y</VoiceAlarm>
<AutoDeal>N</AutoDeal>
<AutoOpen>Y</AutoOpen>
<Temperature>0</Temperature>
<IsOpen>Y</IsOpen>
<StartAlarm>Y</StartAlarm>
<DetectIntervalReal>1</DetectIntervalReal>
<AutoState>Y</AutoState>
<AutoStateTime>10</AutoStateTime>
<ControllerTime>30</ControllerTime>
</Config>需要从xml的AutoDeal读取时y还是n,然后触发器做相应操作.
SQLServer2005 XML在T-SQL查询中的典型应用
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
DECLARE @FileName NVARCHAR(255)
DECLARE @ExecCmd NVARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents NVARCHAR(4000) CREATE TABLE #tempXML
(
PK INT NOT NULL IDENTITY(1, 1),
ThisLine nVARCHAR(255)
)
SET @FileName = 'C:\Config.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXMLEXEC MASTER.dbo.xp_cmdshell @ExecCmdSELECT @y = COUNT(*)
FROM #tempXMLSET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine
FROM #tempXML
WHERE PK = @x
END
--SELECT @FileContents AS FileContents
DROP TABLE #tempXML
DECLARE @DocHandle INT DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument=@filecontentsEXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT AutoDeal FROM OPENXML (@DocHandle, '/Config',2)WITH (AutoDeal nvarchar(10))
PRINT @DocHandle
EXEC sp_xml_removedocument @DocHandle2005中可以,
2000报错
XML 分析错误: 不支持从当前编码到指定编码的切换。
<?xml version="1.0" encoding="gb2312"?>
gb2312不行