OPENXML - SQL Server

Question

利用SQL Server中的OPENXML读取XML文件数据。

这里面有个小问题。这是xml文件部分

<Name_Address> 
    <name>JCB SALES PVT</name>
    <address>24, SALAROURIA ARENA ADUGODI</address>
    <address>HOSUR MAIN ROAD, Honolulu</address>
    <country>N</country>
</Name_Address>

我的SQL查询是

SELECT 
   @address = CONVERT(VARCHAR(150), [TEXT]) 
FROM OPENXML(@idoc,'/Name_Address/address', 0) 
WHERE [text] IS NOT NULL

在@address中，我正在获取最后一个地址标记值，即

HOSUR MAIN ROAD, Honolulu

但它应该是

24, SALAROURIA ARENA ADUGODI, HOSUR MAIN ROAD, Honolulu

我如何才能做到这一点？

帮帮我，引导我做这件事。

问候

Answer 1

您的问题并不是专门针对OPENXML的。

您的问题...

SELECT CONVERT(VARCHAR(150), [TEXT]) 
FROM OPENXML(@idoc,'/Name_Address/address', 0) 
WHERE [text] IS NOT NULL

...returns多行。所以当你赋值给一个变量时，它只需要返回的最后一行。

我已经设置了一个使用游标遍历的示例。它包括您的示例文档。您可以将其直接粘贴到Query Analyser (2000)/Management Studio (2005+)中，它将运行。您所要做的就是添加逗号和空格(我刚刚使用了一个空格)。

DECLARE @hdoc int
DECLARE @doc varchar(1000)
DECLARE @address varchar(150)
DECLARE @thisaddress varchar(150)
set @address = ''
SET @doc ='
<Name_Address> 
    <name>JCB SALES PVT</name>
    <address>24, SALAROURIA ARENA ADUGODI</address>
    <address>HOSUR MAIN ROAD, Honolulu</address>
    <country>N</country>
</Name_Address>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

DECLARE addr_cursor CURSOR
    FOR SELECT CONVERT(VARCHAR(150), [TEXT]) 
    FROM OPENXML(@hdoc,'/Name_Address/address', 0) 
    WHERE [text] IS NOT NULL

--select @@FETCH_STATUS
OPEN addr_cursor
FETCH NEXT FROM addr_cursor INTO @thisaddress

WHILE @@FETCH_STATUS = 0
BEGIN
    set @address = @address+ @thisaddress + ' '
    FETCH NEXT FROM addr_cursor INTO @thisaddress
END

select @address

CLOSE addr_cursor
DEALLOCATE addr_cursor

exec sp_xml_removedocument @hdoc