SQL Server - 对于 XML - 从元素中获取空值

Question

我试图在进行连接时将空 xml 元素替换为空值。我犯了一些愚蠢的错误。我想区分空值和空值。我正在使用 OpenXML 解析 XML 数据，但代码中缺少某些内容来读取基于 null 的参数元素。

我使用的是 Server Server 2014。

请提出建议。

DECLARE @message_body XML;
DECLARE @XMLParameterData Table 
                        (SeqID INT Identity(1,1), 
                         ParamValue varchar(max))

DECLARE @docRef int
DECLARE @dataPath nvarchar(255) 
DECLARE @mappingType int = 2  --Element-Centric mapping

Select   @message_body = N'<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                                                    <ParamList>                                                                 <Param>Bruce</Param>
                                                                    <Param>Wa''yne</Param>
                                                                    <Param>Bruce@karan.com</Param>
                                                                    <Param>Coke</Param>
                                                                    <Param>20000</Param>                                                                    
                                                                    <Param xsi:nil="true"/>
                                                                    <Param></Param>
                                                                </ParamList>
                                                    </AsyncRequest>';


Set @dataPath = '/AsyncRequest/ParamList/Param'
EXEC sp_xml_preparedocument @docRef output, @message_body

INSERT INTO @XMLParameterData(ParamValue)
        Select * From OpenXML(@docRef, @dataPath, @mappingType)
                    WITH
                            (                               
                                valx varchar(max) '.'
                            )

-- the xml document ref needs to be released ASAP       
EXEC sp_xml_removedocument @docRef  

SELECT * From @XMLParameterData
DECLARE @CSVString varchar(max)
SELECT  @CSVString = STUFF( 
                            (SELECT ', ' +  
                            CHAR(34) + ParamValue + CHAR(34)
                             FROM @XMLParameterData
                             ORDER BY SeqID
                             FOR XML PATH('')                                
                             ), 1, 1, '')

SELECT  @CSVString as CSVTest

输出:- “布鲁斯”、“Wa'yne”、“Bruce@karan.com”、“可口可乐”、“20000”、“”、“”

期望的输出:- "Bruce", "Wa'yne", "Bruce@karan.com", "Coke", "20000", NULL, ""

Answer 1

保持简单!使用 CASE WHEN 检查是否 @xsi:nil="true" 和 .nodes 而不是 OPENXML:

DECLARE @message_body XML,
        @output nvarchar(max);

select   @message_body = N'<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ParamList>                                                                 
        <Param>Bruce</Param>
        <Param>Wa''yne</Param>
        <Param>Bruce@karan.com</Param>
        <Param>Coke</Param>
        <Param>20000</Param>                                                                    
        <Param xsi:nil="true"/>
        <Param></Param>
    </ParamList>
</AsyncRequest>';

SELECT @output = STUFF((
    SELECT 
            CASE WHEN t.v.value('@xsi:nil','nvarchar(max)') = 'true' THEN ',NULL' 
                    ELSE ',"'+t.v.value('.','nvarchar(max)') + '"' 
                        END
    FROM @message_body.nodes('AsyncRequest/ParamList/Param') as t(v)
    FOR XML PATH('')
),1,1,'')

SELECT @output 

将返回:

"Bruce","Wa'yne","Bruce@karan.com","Coke","20000",NULL,""