OPENXML - SQL

[Hum Dum]

My Sp reading XML file data with help of OPENXML in SQL. There is slight problem in this.

<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

[Hiren solanki]

Hum Dum wrote:

SELECT @address = CONVERT(VARCHAR(150),[TEXT]) FROM OPENXML(@idoc,'/Name_Address/address', 0) where [text] is not null

AFAIK the result is having two rows returning and At a time of converting SQL might picking up the second row and converting it accordingly, As It returns two rows having one column. But I can Provide you another solution for that. To first Retrieve the result and using COALESCE you can JOIN them together to produce expected result. Here's my work for you only.

SELECT @address = COALESCE(@address+',' ,'') + [address]
FROM
(
SELECT * FROM
OPENXML (@idoc, 'Name_Address/address',3)
WITH ([address] varchar(1000) '.')
) P
SELECT @address

It's working all right.

Regards, Hiren. Microsoft Dynamics CRM My Recent Article: - Way to know which control have raised PostBack[^]