1

I am working on a project where I have two little tables that store XML value. My requirement is to pull out columns based on conditions.

  1. I have a variable tempvar. When tempvar = 'H', I want all the nodes from both the tables to show up in XML fashion

  2. When tempvar = 'P', I want all the nodes from the first table and only one node from the second table to show up in XML fashion

Rexter link: http://rextester.com/BSRX9069

I have only created the temp tables. And included desired output for each condition in the rexter.

No clue where to start. Any help?

3
  • Hey Rick, pictures of code = evil, can you copy and paste it so we can copy and paste it. Commented Jun 13, 2018 at 20:48
  • @HolmesIV haha ok will do in a min Commented Jun 13, 2018 at 21:04
  • question edited. New rexter linjk. Pls chk Commented Jun 13, 2018 at 21:06

1 Answer 1

1

I don't know if I got this correctly:

create table #temp1(xml_data xml)
insert into #temp1
select 
xml_data = 
(select 
     cid = '1001',
     name = 'tim',
     age = '15',
     req = '1',
     part = '1'
 for xml path(''),type)

create table #temp2(xml_data xml)
insert into #temp2
select 
xml_data = 
(select 
     cid = '1001',
     education = '5',
     resi = 'Yes',
     active = '1'
 for xml path(''),type);

--Declare your variable

DECLARE @YourVar CHAR(1)='H';

--XML will omit any values which are NULL. The CASE in the final SELECT will set all not wanted values to NULL. The effect: They will not show up in the final XML:

WITH t1 AS
(
    SELECT xml_data.value(N'(cid/text())[1]',N'int') AS cid
          ,xml_data.value(N'(name/text())[1]',N'nvarchar(max)') AS [name]
          ,xml_data.value(N'(age/text())[1]',N'int') AS age
          ,xml_data.value(N'(req/text())[1]',N'int') AS req
          ,xml_data.value(N'(part/text())[1]',N'int') AS part
    FROM #temp1
)
,t2 AS
(
    SELECT xml_data.value(N'(cid/text())[1]',N'int') AS cid
          ,xml_data.value(N'(education/text())[1]',N'int') AS education
          ,xml_data.value(N'(resi/text())[1]',N'nvarchar(max)') AS resi
          ,xml_data.value(N'(active/text())[1]',N'int') AS active
    FROM #temp2
)
SELECT t1.*
      ,CASE WHEN @YourVar='H' THEN t2.education END AS education
      ,CASE WHEN @YourVar='H' THEN t2.resi END AS resi
      ,CASE WHEN @YourVar='H' THEN t2.active END AS active
FROM t1
INNER JOIN t2 ON t1.cid=t2.cid
FOR XML PATH('root');

--clean-up
go
drop table #temp1, #temp2
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.