Including 'bit' values from SQL to JSON

I have a sproc workflow as below:

1. Table #ans(qid int, ans varchar(50), aid varchar(10))
2. Table #temp1(cid int, [q1] bit)
3. Table #final(XML_data varchar(max)

Rextester: http://rextester.com/PUX22792

The temp1 table has a column q1 which is actually grouped from #ans table and aid column which is varchar. The q1 is declared bit, however it comes out of varchar column. So on the final XML table, I select this column [q1] as it is and when I conver the XML output to JSON, I can still see that column to be string and not bit. I have created a rextester link wit all the sample data. Any help will be appreciated!

I don't see any problem in how your SQL is laid out, its just the way the transformation tool you are using for XML to JSON handles bit data type. Unless you are using XML Schema Definitions (XSDs), XML does not clearly delineate between data types, whereas JSON is strongly typed where Numbers, booleans, strings, empty strings, empty objects and nulls can all be clearly specified. So if you look at below xml element(s), it can be either or:

<StringOrNumber>12345</StringOrNumber> <StringOrBoolean>false</StringOrBoolean>

So the default XML to JSON transformation treats everything as string, and thus you are seeing the bit 0/1 as string representation "0" & "1". In your middle ware application code, you will have to use a transformation library with xml & xsd as input to get correct JSON result.

I made corrections to your SQL to make it executable:

--Table 1 to store all answers
create Table #ans(qid int, ans varchar(50), aid varchar(10));
insert into #ans values
       (1001,'test answer',null),
      (1002,null,'a'),
      (1003,null,'0'),
      (1004,null,'1');

--Table 2 to pivot answers
create table #temp(cid int,[1001] varchar(50),[1002] varchar(10), [1003] bit, [1004] bit);
insert into #temp
select 12345,
        max(case when qid = '1001' then a.ans end) as [1001],
         max(case when qid = '1002' then a.aid end) as [1002],
          max(case when qid = '1003' then a.aid end) as [1003],
           max(case when qid = '1004' then a.aid end) as [1004]
from #ans a;

--Doing an XML final select
create table #final (xml_data varchar(max));
insert into #final
select 
final_data = 
      (select
        fakedata = 'something..',
        question1 = t.[1001],
        question2 = t.[1001],
        question3 = t.[1003],
        question4 = t.[1004]
        from #temp t
      FOR XML PATH(''));

select * from  #ans;
select * from  #temp;
select * from  #final;

And this is the result looks like:

So if you use this online transformer for xml to json converter (ofcourse it doesn't take xsd for transformation). It will transform your xml data to json like below:

<root>
<fakedata>something..</fakedata><question1>test answer</question1><question2>test answer</question2><question3>0</question3><question4>1</question4>
</root>


{
   "fakedata": "something..",
   "question1": "test answer",
   "question2": "a",
   "question3": "1",
   "question4": "0"
}

whereas you need below xsd for right transformation:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="fakedata"/>
        <xs:element type="xs:string" name="question1"/>
        <xs:element type="xs:string" name="question2"/>
        <xs:element type="xs:boolean" name="question3"/>
        <xs:element type="xs:boolean" name="question4"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

{
   "fakedata": "something..",
   "question1": "test answer",
   "question2": "a",
   "question3": true,
   "question4": false
}