Need Single Row Result using openrowset

Question

I have an xml which is having images tag and inside this there is 12 URL tag.

I have write this query to fetch the result from xml.

Hotel.xml File :

<images>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
  <url></url>
 </images>

Here is my code :

CREATE  TABLE #WorkingTable ( Data XML )
INSERT  INTO #WorkingTable
        SELECT  *
        FROM    OPENROWSET(BULK 'd:\hotels.xml', SINGLE_BLOB) AS data


DECLARE @XML AS XML ,
    @hDoc AS INT

SELECT  @XML = Data
FROM    #WorkingTable


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


declare @tmp varchar(MAX)
SET @tmp = ''

SELECT  pref.value('(hotel_ref/text())[1]','varchar(400)')AS hotel_ref,  
sref.value('(text())[1]', 'varchar(400)')+ ';' 
FROM        #WorkingTable CROSS APPLY
  Data.nodes('//hotels/hotel') AS hotel(pref)
  CROSS APPLY
  pref.nodes('images/url') AS images(sref) 


EXEC sp_xml_removedocument @hDoc

DROP TABLE #WorkingTable

My Problem is that it is returning 12 rows . i need URL values to be Comma Separated. How it is possible.

Answer 1

You don't need the temp table and you don't need the calls to sp_xml*.

Load your XML like this.

declare @XML xml
set @XML = 
  (
  select *
  from openrowset(bulk 'd:\hotels.xml', single_blob) as data  
  )

The XML you have provided is a poor match to the query you are using.
Here is something that builds a comma separated string with the XML in your question.

select
  (
  select ', '+T.N.value('text()[1]', 'varchar(400)')
  from @XML.nodes('images/url') as T(N)
  for xml path(''), type
  ).value('substring(text()[1], 3)', 'varchar(max)')