how to multiple values to subquery that one xml field


Keywords:sql  server 


Question: 

declare @x xml;
declare @DocHandle int;
select @x=(select EmployeeAllwDedDetail1.EmpAllowanceDeduction from EmployeeAllwDedDetail1 where EmployeeKey in(select EmployeeKey from EmployeeMaster where EmployeeMaster.EmployeeID in(SELECT * FROM string_split('136,137', ',')) ) 
    --and MONTH(EmployeeAllwDedDetail1.EffectiveDate) = 2
    --and YEAR(EmployeeAllwDedDetail1.EffectiveDate) = 2018)

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @x  
-- Execute a SELECT statement using OPENXML rowset provider.  
declare @temptbl1 table(Name1 varchar(20),Amount decimal(10,2))
declare @temptbl2 table(Name1 varchar(20),Amount decimal(10,2))

insert into @temptbl1
SELECT *  
FROM OPENXML (@DocHandle, '/AllowanceDeduction/Allowance',1)  
      WITH (Name  varchar(10),  
            Amount decimal(10,2))  
 EXEC sp_xml_removedocument @DocHandle 

 EXEC sp_xml_preparedocument @DocHandle OUTPUT, @x
 insert into @temptbl2  
 SELECT *  
FROM OPENXML (@DocHandle, '/AllowanceDeduction/Deduction',1)  
      WITH (Name  varchar(10),  
            Amount decimal(10,2))  
EXEC sp_xml_removedocument @DocHandle  
;with cteTemp as(
select * from @temptbl1 union all
select * from @temptbl2)

select *  from cteTemp

Actaully i am trying to run this query to return multiple values but it is returning single value when i put the cindu=ition exactly for one record otherwise it shows sub-query error ..can you please forward me solution for that


1 Answer: 

Problem is when you provide multiple comma separated values in string_split function. You are trying to assign multiple values to @x variable. Try below code (Here we are using variable table to hold multiple values and using while loop) -

declare @tmp_tbl table (id int identity(1,1), xml_data xml);
declare @x xml;
declare @DocHandle int;
declare @max int, @count int=1;
declare @temptbl1 table(Name1 varchar(20),Amount decimal(10,2))
declare @temptbl2 table(Name1 varchar(20),Amount decimal(10,2))

insert into @tmp_tbl (xml_data)
select EmployeeAllwDedDetail1.EmpAllowanceDeduction from EmployeeAllwDedDetail1 
where EmployeeKey in(select EmployeeKey from EmployeeMaster where EmployeeMaster.EmployeeID in(SELECT * FROM string_split('136,137', ','))) 
    --and MONTH(EmployeeAllwDedDetail1.EffectiveDate) = 2
    --and YEAR(EmployeeAllwDedDetail1.EffectiveDate) = 2018)

select @max = max(id) from @tmp_tbl

while @count <= @max
begin
    select @x = xml_data from @tmp_tbl where id = @count

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @x  
    -- Execute a SELECT statement using OPENXML rowset provider.  

    insert into @temptbl1
    SELECT *  
    FROM OPENXML (@DocHandle, '/AllowanceDeduction/Allowance',1)  
          WITH (Name  varchar(10),  
                Amount decimal(10,2))  
     EXEC sp_xml_removedocument @DocHandle 

     EXEC sp_xml_preparedocument @DocHandle OUTPUT, @x

     insert into @temptbl2  
     SELECT *  
    FROM OPENXML (@DocHandle, '/AllowanceDeduction/Deduction',1)  
          WITH (Name  varchar(10),  
                Amount decimal(10,2))  
    EXEC sp_xml_removedocument @DocHandle  

    set @count = @count+1
end

;with cteTemp as(
select * from @temptbl1 union all
select * from @temptbl2)

select *  from cteTemp