Включая битовые значения из SQL в JSON

Question

У меня есть рабочий процесс sproc, как показано ниже:

1. Таблица #ans(qid int, ans varchar(50), помощь varchar(10))
2. Таблица #temp1(cid int, [q1] bit)
3. Таблица #final (XML_data varchar (max)

Rextester: http://rextester.com/PUX22792

В таблице temp1 есть столбец q1, который фактически сгруппирован из таблицы #ans, и столбец помощи, который является varchar. Q1 объявлен битом, однако он выходит из столбца varchar. Итак, в итоговой таблице XML я выбираю этот столбец [q1] как есть, и когда я конвертирую вывод XML в JSON, я все равно вижу этот столбец как строковый, а не как битовый. Я создал ссылку rextester со всеми образцами данных. Любая помощь будет оценена!

Answer 1

Я не вижу никаких проблем в том, как выстроен ваш SQL, просто как инструмент преобразования, который вы используете для XML в JSON, обрабатывает битовый тип данных. Если вы не используете определения схемы XML (XSD), XML не может четко разграничить типы данных, в то время как JSON строго типизирован, где могут быть четко определены числа, логические значения, строки, пустые строки, пустые объекты и нули. Поэтому, если вы посмотрите на элемент (ы) ниже xml, это может быть либо:

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

Таким образом, преобразование XML в JSON по умолчанию обрабатывает все как строку, и поэтому вы видите бит 0/1 как строковое представление "0" и "1". В вашем программном коде промежуточного программного обеспечения вам нужно будет использовать библиотеку преобразований с xml & xsd в качестве входных данных, чтобы получить правильный результат JSON.

Я внес исправления в ваш SQL, чтобы сделать его исполняемым:

--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;

И вот результат выглядит так:

Так что если вы используете этот онлайн-преобразователь для преобразования xml в json (конечно, для преобразования не требуется xsd). Это преобразует ваши XML-данные в JSON, как показано ниже:

<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"
}

тогда как вам нужно ниже xsd для правильного преобразования:

<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
}