subquery returned more than 1 value. How do I fix this

Question

I'm trying to get the output <xp><p id="aa"><v>hi</v></p><p id="bb"><v>care</v></p></xp>

out of the below query.But end up getting an error subquery returns more than 1. ANy help would be greatly apreciated

Below is my SQL query

declare @tmp1 table(ProductId nvarchar(256),Extended nvarchar(max))
insert into @tmp1 values(1,'

<xp><p id="181"><v>@1@</v></p><p id="184"> 
    <v>@2@</v></p></xp>')

declare @tmp2 table(ID int,Descr nvarchar(max))
insert into @tmp2 values(181,'aa')
insert into @tmp2 values(184,'bb')


declare @tmp5 table(ID int,Name nvarchar(max),propID int)
insert into @tmp5 values(1,'hi',181)
insert into @tmp5 values(2,'how',181)
insert into @tmp5 values(3,'are',181)
insert into @tmp5 values(1,'take',184)
insert into @tmp5 values(2,'care',184)
insert into @tmp5 values(3,'bye',184)


declare @tmp3 table(ProductId nvarchar(256),Extended nvarchar(max),rownum int)
insert into @tmp3
select ProductId,
Isnull((Select  t5.name from @tmp5 t5 inner join @tmp2 t2 on t5.id + ',' + 
t5.propID =item + ',' + t2.id and t5.propID=t2.id and  
convert(nvarchar(max),t5.id)= item),item)Extended
,DENSE_RANK()Over( order by productid ) 
 from @tmp1 t1
CROSS APPLY dbo.SplitString([Extended] ,'@')  
select * from @tmp3

Answer 1

There are some things you really should avoid:

• store XML as NVARCHAR()
• think of HTML as the same as XML (unless it is strict XHTML)
• deal with XML with string methods

As long as this is valid XML (or XHTML) you can try this:

Your sample

declare @tmp1 table(ProductId nvarchar(256),Extended nvarchar(max))
declare @tmp1 table(ProductId nvarchar(256),Extended nvarchar(max))
insert into @tmp1 values('P1','<xp><p id="181"><v>1</v></p><p id="184"><v>2</v></p></xp>') 
                       ,('P2','<xp><p id="184"><v>3</v></p><p id="184"><v>1</v></p></xp>') 

declare @tmp2 table(ID int,Descr nvarchar(max))
insert into @tmp2 values(181,'aa')
insert into @tmp2 values(184,'bb')


declare @tmp5 table(ID int,Name nvarchar(max),propID int)
insert into @tmp5 values(1,'hi',181)
insert into @tmp5 values(2,'how',181)
insert into @tmp5 values(3,'are',181)
insert into @tmp5 values(1,'take',184)
insert into @tmp5 values(2,'care',184)
insert into @tmp5 values(3,'bye',184)

--the query

SELECT ProductId
      ,(
        SELECT t2.Descr     AS [@id]
              ,t5.[Name]    AS [v]
        FROM A.Casted.nodes('/xp/p') B(p)
        CROSS APPLY (SELECT B.p.value('@id','int') AS p_id
                           ,REPLACE(B.p.value('(v/text())[1]','nvarchar(max)'),'@','') AS p_Val) C
        INNER JOIN @tmp2 t2 ON C.p_id=t2.ID
        INNER JOIN @tmp5 t5 ON t5.propID=t2.ID AND t5.ID=C.p_Val
        FOR XML PATH('p'),ROOT('xp'),TYPE
      ) AS YourXmlDetails
FROM @tmp1 t1
CROSS APPLY (SELECT CAST(t1.Extended AS XML) Casted) A;

the result (e.g. one line)

<xp>
  <p id="aa">
    <v>hi</v>
  </p>
  <p id="bb">
    <v>care</v>
  </p>
</xp>

The idea in short:

• We use APPLY to get your XML in a typed column (better was a typed column in the source table)
• We use APPLY to read the <p> elements and one more APPLY to retrieve the values.
• We join the other tables
• We build a new XML using FOR XML

Hint: You are using a string splitting function to deal with the @. This might point to multiple values like @1@2@, but your given sample does not show so. If you'd need this, you can try to split the returned p_Val and join to this list.