OPENXML - SQL Server

My Sp reading XML file data with help of OPENXML in SQL Server.

There is slight problem in this. Here is xml file part

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

and开发者_运维百科 my SQL query is

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

In @address I am getting last address tag value i.e

HOSUR MAIN ROAD, Honolulu

But it should be

24, SALAROURIA ARENA ADUGODI, HOSUR MAIN ROAD, Honolulu

How can I achieve this ?

Help me, guide me to do this.

regards

Your problem isn't specifically to do to with OPENXML.

Your query...

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

...returns multiple rows. So when you assign to a variable, it just takes the last of the returned rows.

I've set up an example which uses a cursor to iterate through this. It includes your example document. You can paste this directly in Query Analyser (2000)/Management Studio (2005+) and it will run. all you have to do is add in your commas and spaces (I've just used a space).

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