OPENXML - SQL Server

Question

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

Well worded question, and in answering I learned something. +1 — Mutation Person
– Mutation Person, Commented Dec 9, 2010 at 8:24
Where are you getting this horrific XML from? Can you push back to whoever is supplying it for them to supply something more sane? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 9, 2010 at 9:07

Mutation Person · Accepted Answer · 2010-12-09 08:22:34Z

3

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

answered Dec 9, 2010 at 8:22

Mutation Person

30.6k18 gold badges102 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Humdum Over a year ago

it works. How you came to know about this. what i want to know How can a person increase his skills and programming ability? If you can guide me, the best way of Self Help?

Mutation Person Over a year ago

@Humdum - Could you mark this as accepted by clicking on the tick. Very difficult to answer your skill/ability question. Experience and an inquisitive mind helps. For example, I hadn't even heard of OPENXML on SQL Server, but I looked it up and wrote a quick example. I decided to see what would happen if I did a select, instead of an assignment on your original SELECT @address=.... statement. That revealed both rows were being returned, so I inferred that only the last was being set. There is much more about experience, etc here on StackOverflow, and programmers.stackexchange.com.

Collectives™ on Stack Overflow

OPENXML - SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related