SQL Server, определенная XML-схема для столбца

Question

Я пытаюсь добавить схему XML в столбцы моей таблицы. что я хотел бы сделать, это:

• извлечь схему из запроса
• добавить схему в столбец
• вставить XML в этот столбец

Моя проблема заключается в том, как исключить некоторые параметры в схеме, например, возможность не проверять пустые элементы.

так, например:

create table #t1 (c1 int not null,c2 int null)

  select CONVERT(xml,(select *
    from #t1
    where 0=1
    for xml raw,elements,XMLSCHEMA('XXX')
    ))

это поймай меня

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="XXX" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="c1" type="sqltypes:int" />
        <xsd:element name="c2" type="sqltypes:int" minOccurs="0" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Как определить все элементы с "minOccurs="0""??

благодарю вас!

Answer 1

Есть - точно! - не легко! Двигатель обязан вернуть схему так, как она была найдена. Вы можете взять схему как XML и измените его, вы можете использовать строковый подход (или какое-то регулярное выражение). Ниже приведен причудливый, но работающий динамический подход:

Процедура sp_describe_first_result_set используется для получения метаданных неизвестного набора результатов. Вы можете сохранить его возврат в таблице.

Используя информацию, вы можете создать копию своей таблицы, где все столбцы обнуляются. Используйте эту новую таблицу, чтобы получить вашу схему:

create table #FirstResult(is_hidden BIT
                         ,column_ordinal INT
                         ,name NVARCHAR(MAX)
                         ,is_nullable BIT
                         ,system_type_id BIT
                         ,system_type_name VARCHAR(MAX)
                         ,max_length INT
                         ,[precision] INT
                         ,scale INT
                         ,collation_name VARCHAR(MAX)
                         ,user_type_id INT
                         ,user_type_database VARCHAR(MAX)
                         ,user_type_schema VARCHAR(MAX)
                         ,user_type_name VARCHAR(MAX)
                         ,assembly_qualified_type_name VARCHAR(MAX)
                         ,xml_collection_id VARCHAR(MAX)
                         ,xml_collection_database VARCHAR(MAX)
                         ,xml_collection_schema VARCHAR(MAX)
                         ,xml_collection_name VARCHAR(MAX)
                         ,is_xml_document BIT
                         ,is_case_sensitive BIT
                         ,is_fixed_length_clr_type BIT
                         ,source_server VARCHAR(MAX)
                         ,source_database VARCHAR(MAX)
                         ,source_schema VARCHAR(MAX)
                         ,source_table VARCHAR(MAX)
                         ,source_column VARCHAR(MAX)
                         ,is_identity_column BIT
                         ,is_part_of_unique_key BIT
                         ,is_updateable BIT
                         ,is_computed_column BIT
                         ,is_sparse_column_set BIT
                         ,ordinal_in_order_by_list INT
                         ,order_by_is_descending BIT
                         ,order_by_list_length INT
                         ,tds_type_id INT
                         ,tds_length INT
                         ,tds_collation_id INT
                         ,tds_collation_sort_id INT);

- Ваш вариант использования

create table #t1 (c1 int not null,c2 int null);
GO

INSERT INTO #FirstResult
exec sp_describe_first_result_set @tsql=N'SELECT * FROM #t1';

DECLARE @cmd NVARCHAR(MAX)=
(
SELECT N'DECLARE @tmp TABLE('
+
STUFF(
(
SELECT N',' + name + ' ' + system_type_name + N' NULL' 
FROM #FirstResult
FOR XML PATH(''),TYPE
).value('text()[1]','nvarchar(max)'),1,1,'')
+N'); 
SELECT * FROM @tmp AS SomeTable FOR XML RAW,ELEMENTS,XMLSCHEMA(''XXX'')'
);
EXEC (@cmd);

GO
drop table #t1;
drop table #FirstResult;
GO

Результат

<xsd:schema targetNamespace="XXX" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="c1" type="sqltypes:int" minOccurs="0" />
        <xsd:element name="c2" type="sqltypes:int" minOccurs="0" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>  

Answer 2

Если вы не возражаете, разница между minOccurs="0" и nillable ( http://www.dimuthu.org/blog/2008/08/18/xml-schema-nillabletrue-vs-minoccurs0/comment-page-1/) тогда вы можете сделать

create table #t1 (c1 int not null,c2 int null)

  select CONVERT(xml,(select *
    from #t1
    where 0=1
    for xml raw,elements xsinil,XMLSCHEMA('XXX')
    ))