解析复杂XML遇到的问题

[yangzeDB]

1. 如果为空值.怎么办?
2. 为什么是汉字就报错.字母不会 (报错：XML 分析错误: 文本内容中发现无效字符。)
3. 如果数字是科学记数法怎么办！
4. "你好世界"我要显示到一个字段里。如何操作
这些都是实际开发中遇到的问题。用2000.

DECLARE @idoc int;
DECLARE @doc varchar(4000);
SET @doc ='
	<Data value="200712">
			<Assets>
				<FixedAssets>1056000210009.2333</FixedAssets>
			</Assets>
			<Liabilities>
				<CurrentLiabilities>你好</CurrentLiabilities>
				<CurrentLiabilities>世界</CurrentLiabilities>
			</Liabilities>
			<MinorityInterests>3406000000</MinorityInterests>
	</Data>
'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc  
 SELECT * FROM OPENXML (@Idoc, '/Data/Assets/FixedAssets',2)WITH (
			filed varchar(10) '/Data/@value'
			,dt numeric(18,3)  '/Data/Assets/FixedAssets'
			,METHOD varchar(20) '/Data/Liabilities/CurrentLiabilities'
			,tp numeric'/Data/MinorityInterests'
)
exec sp_xml_removedocument @Idoc

[liangCK]

DECLARE @idoc int;
DECLARE @doc varchar(4000);
SET @doc ='
  <?xml version="1.0" encoding="gbk"?>
    <Data value="200712">
            <Assets>
                <FixedAssets>1056000210009.2333</FixedAssets>
            </Assets>
            <Liabilities>
                <CurrentLiabilities>你好</CurrentLiabilities>
                <CurrentLiabilities>世界</CurrentLiabilities>
            </Liabilities>
            <MinorityInterests>3406000000</MinorityInterests>
    </Data>
'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc  
 SELECT * FROM OPENXML (@Idoc, '/Data/Assets/FixedAssets',2)WITH (
            filed varchar(10) '/Data/@value'
            ,dt numeric(18,3)  '/Data/Assets/FixedAssets'
            ,METHOD varchar(20) '/Data/Liabilities/CurrentLiabilities'
            ,tp numeric'/Data/MinorityInterests'
)
exec sp_xml_removedocument @Idoc

[liangCK]

哦..不止一个要求..

[liangCK]

DECLARE @idoc int;
DECLARE @doc xml;
SET @doc ='
    <Data value="200712">
            <Assets>
                <FixedAssets>1056000210009.2333</FixedAssets>
            </Assets>
            <Liabilities>
                <CurrentLiabilities>你好</CurrentLiabilities>
                <CurrentLiabilities>世界</CurrentLiabilities>
            </Liabilities>
            <MinorityInterests>3406000000</MinorityInterests>
    </Data>
'

SELECT filed=T.x.value('../../@value','int'),
       FixedAssets=T.x.value('.','VARCHAR(50)'),
       METHOD=T.x.query('for $i in ../../Liabilities
                           return string($i)').value('.','VARCHAR(20)'),
       tp=T.x.value('(../../MinorityInterests)[1]','VARCHAR(20)')
FROM @doc.nodes('/Data/Assets/FixedAssets') AS T(x)

/*
filed       FixedAssets                                        METHOD               tp
----------- -------------------------------------------------- -------------------- --------------------
200712      1056000210009.2333                                 你好世界                 3406000000

(1 行受影响)


*/

[Garnett_KG]

1. 如果为空值.怎么办?

不知道你的为空是不是指null
若是，在xml字串中删除字段的定义就行了.

[Quote]
2. 为什么是汉字就报错.字母不会 (报错：XML 分析错误: 文本内容中发现无效字符。)
[/Quote]
用NVARCHAR、NTEXT定义xml字串.

[Quote]
3. 如果数字是科学记数法怎么办！
[/Quote]
定义成float

[Quote]
4. "你好世界"我要显示到一个字段里。如何操作
[/Quote]
看示例:

DECLARE @idoc int;
DECLARE @doc nvarchar(4000);  ---NVARCHAR(4000)
SET @doc =N'
    <Data value="200712">
            <Assets>
                <FixedAssets>1056000210009.2333</FixedAssets>
            </Assets>
            <Liabilities>
                <CurrentLiabilities>你好</CurrentLiabilities>
                <CurrentLiabilities>世界</CurrentLiabilities>
            </Liabilities>
            <MinorityInterests>3406000000</MinorityInterests>
	    <price>3.3E-3</price>
    </Data>
'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc  
SELECT * 
FROM OPENXML (@Idoc, '/Data/Assets/FixedAssets',2)WITH (
            filed varchar(10) '/Data/@value'
            ,dt numeric(18,3)  '/Data/Assets/FixedAssets'
            ,METHOD varchar(20) '/Data/Liabilities' /*你好 世界*/
            ,tp numeric'/Data/MinorityInterests'
	    ,price float '/Data/price'  /*科学记数法*/
	    ,nullvalue varchar(10)      /*null值*/
)
exec sp_xml_removedocument @Idoc

[liangCK]

2000不是很熟..貌似需要将那些要求都整到前台去..让前台程序去整..

[fcuandy]

.

[liangCK]

哦..学习KG的..