none
Annotated XSD, XPath queries extremely slow

    Question

  • I am continuing to evaluate annotated XML schemas to return XML to a client from SQL Server.

    ( see: http://msdn.microsoft.com/en-us/library/ms171956.aspx )

    I am finding that the TSQL that is produced when executing the XPath query for the entire XSD document creates a SQL statement that is extremely huge, and takes 5 - 10 seconds to return. This is regardless of there being data in the database.

    I find that the SQL query that is produced becomes exponentially slower when you start to create depth within the XSD.  

    E.g. creating multiple elements in the heirarchy with the sql:is-constant="1".

    Can anyone shed any light on this, as it seems that SQLXML using XSD's is not feasible in a production environment.  If I were to continue along this path, the length of time to return a relatively small amount of data will be close to 20 seconds.

    Here's a simple example of transforming the content of two tables.  In my test, both tables only have *one* row of data.  To return the result from the SQLServer it takes 612ms to process (confirmed through the profiler), and the TSQL query that is produced is absolutely enormous.

    Table definitions:

    CREATE TABLE [dbo].[Table_1](
    	[Guid] [uniqueidentifier] NOT NULL,
    	[C1] [nchar](10) NULL,
    	[C2] [nchar](10) NULL,
    	[C3] [nchar](10) NULL,
    	[C4] [nchar](10) NULL,
    	[C5] [nchar](10) NULL,
    	[C6] [nchar](10) NULL,
    	[C7] [nchar](10) NULL,
    	[C8] [nchar](10) NULL,
     CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED 
    (
    	[Guid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[Table_2](
    	[Guid] [uniqueidentifier] NOT NULL,
    	[Table1Guid] [uniqueidentifier] NOT NULL,
    	[C1] [nchar](10) NULL,
    	[C2] [nchar](10) NULL,
    	[C3] [nchar](10) NULL,
    	[C4] [nchar](10) NULL,
    	[C5] [nchar](10) NULL,
    	[C6] [nchar](10) NULL,
    	[C7] [nchar](10) NULL,
    	[C8] [nchar](10) NULL,
     CONSTRAINT [PK_Table_2_1] PRIMARY KEY CLUSTERED 
    (
    	[Guid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Table_2]  WITH CHECK ADD  CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Table1Guid])
    REFERENCES [dbo].[Table_1] ([Guid])
    GO
    
    ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
    GO
    

    The XSD:

    <?xml version="1.0" encoding="utf-8"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    
    
      <xsd:annotation>
        <xsd:appinfo>
          <sql:relationship name="Table1ToTable2"
              parent="Table_1"
              parent-key="Guid"
              child="Table_2"
              child-key="Table1Guid" />
        </xsd:appinfo>
      </xsd:annotation>
    
      <xsd:element name="TestingDocument" sql:is-constant="1">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="InnerDocument" sql:is-constant="1">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="Table_1" sql:relation="Table_1" sql:key-fields="Guid">
                    <xsd:complexType>
                      <xsd:sequence>                    
                        <xsd:element name="C1-C3-Columns" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="C1"/>
                              <xsd:element name="C2"/>
                              <xsd:element name="C3"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="C4-8-Columns" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="C4"/>
                              <xsd:element name="C5"/>
                              <xsd:element name="C6"/>
                              <xsd:element name="C7"/>
                              <xsd:element name="C8"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="C1-8-Columns" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="C1"/>
                              <xsd:element name="C2"/>
                              <xsd:element name="C3"/>
                              <xsd:element name="C4"/>
                              <xsd:element name="C5"/>
                              <xsd:element name="C6"/>
                              <xsd:element name="C7"/>
                              <xsd:element name="C8"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="Table_2_Data" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="Table_2" sql:relation="Table_2" sql:relationship="Table1ToTable2" sql:key-fields="Guid">
                                <xsd:complexType>
                                  <xsd:sequence>
                                    <xsd:element name="C1"/>
                                    <xsd:element name="C2"/>
                                    <xsd:element name="C3"/>
                                    <xsd:element name="C4"/>
                                    <xsd:element name="C5"/>
                                    <xsd:element name="C6"/>
                                    <xsd:element name="C7"/>
                                    <xsd:element name ="AllVirtualData" sql:is-constant="1">
                                      <xsd:complexType>
                                        <xsd:sequence>
                                          <xsd:element name="VirtualData" sql:is-constant="1">
                                            <xsd:complexType>
                                              <xsd:sequence>
                                                <xsd:element name="SomeBool" fixed="false" />
                                              </xsd:sequence>
                                            </xsd:complexType>
                                          </xsd:element>
                                        </xsd:sequence>
                                      </xsd:complexType>
                                    </xsd:element>
                                    <xsd:element name ="AllSkipDescriptions" sql:is-constant="1">
                                      <xsd:complexType>
                                        <xsd:sequence>
                                          <xsd:element name="SkipDescription" sql:is-constant="1">
                                            <xsd:complexType>
                                              <xsd:sequence>
                                                <xsd:element name="Skip" fixed="true" />
                                              </xsd:sequence>
                                            </xsd:complexType>
                                          </xsd:element>
                                        </xsd:sequence>
                                      </xsd:complexType>
                                    </xsd:element>
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name ="AllAlternateDimensions" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="AlternateDimension" sql:is-constant="1">
                                <xsd:complexType>
                                  <xsd:sequence>
                                    <xsd:element name="UseAlternateDimension" fixed="false" />
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name ="AllSkipDescriptions" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="SkipDescription" sql:is-constant="1">
                                <xsd:complexType>
                                  <xsd:sequence>
                                    <xsd:element name="Skip" sql:is-constant="1" />
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                  <xsd:element name="Table2Dump" sql:relation="Table_2" sql:key-fields="Guid">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:element name="C1-8-Columns" sql:is-constant="1">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="C1"/>
                              <xsd:element name="C2"/>
                              <xsd:element name="C3"/>
                              <xsd:element name="C4"/>
                              <xsd:element name="C5"/>
                              <xsd:element name="C6"/>
                              <xsd:element name="C7"/>
                              <xsd:element name="C8"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>
    

    SQL Query produced:

    select 1 as TAG,0 as parent,0 as [InnerDocument!1!_g_0!hide],NULL as [Table_1!2!Guid!hide],NULL as [C1-C3-Columns!3!_g_2!hide],NULL as [C1!4!_g_3!hide],NULL as [C1!4!],NULL as [C2!5!_g_4!hide],NULL as [C2!5!],NULL as [C3!6!_g_5!hide],NULL as [C3!6!],NULL as [C4-8-Columns!7!_g_6!hide],NULL as [C4!8!_g_7!hide],NULL as [C4!8!],NULL as [C5!9!_g_8!hide],NULL as [C5!9!],NULL as [C6!10!_g_9!hide],NULL as [C6!10!],NULL as [C7!11!_g_10!hide],NULL as [C7!11!],NULL as [C8!12!_g_11!hide],NULL as [C8!12!],NULL as [C1-8-Columns!13!_g_12!hide],NULL as [C1!14!_g_13!hide],NULL as [C1!14!],NULL as [C2!15!_g_14!hide],NULL as [C2!15!],NULL as [C3!16!_g_15!hide],NULL as [C3!16!],NULL as [C4!17!_g_16!hide],NULL as [C4!17!],NULL as [C5!18!_g_17!hide],NULL as [C5!18!],NULL as [C6!19!_g_18!hide],NULL as [C6!19!],NULL as [C7!20!_g_19!hide],NULL as [C7!20!],NULL as [C8!21!_g_20!hide],NULL as [C8!21!],NULL as [Table_2_Data!22!_g_21!hide],NULL as [Table_2!23!C1!element],NULL as [Table_2!23!C2!element],NULL as [Table_2!23!C3!element],NULL as [Table_2!23!C4!element],NULL as [Table_2!23!C5!element],NULL as [Table_2!23!C6!element],NULL as [Table_2!23!C7!element],NULL as [Table_2!23!Guid!hide],NULL as [AllVirtualData!24!_g_23!hide],NULL as [VirtualData!25!_g_24!hide],NULL as [SomeBool!26!_g_25!hide],NULL as [SomeBool!26!],NULL as [AllSkipDescriptions!27!_g_26!hide],NULL as [SkipDescription!28!_g_27!hide],NULL as [Skip!29!_g_28!hide],NULL as [Skip!29!],NULL as [AllAlternateDimensions!30!_g_29!hide],NULL as [AlternateDimension!31!_g_30!hide],NULL as [UseAlternateDimension!32!_g_31!hide],NULL as [UseAlternateDimension!32!],NULL as [AllSkipDescriptions!33!_g_32!hide],NULL as [SkipDescription!34!_g_33!hide],NULL as [Skip!35!_g_34!hide],NULL as [Table2Dump!36!Guid!hide],NULL as [C1-8-Columns!37!_g_36!hide],NULL as [C1!38!_g_37!hide],NULL as [C1!38!],NULL as [C2!39!_g_38!hide],NULL as [C2!39!],NULL as [C3!40!_g_39!hide],NULL as [C3!40!],NULL as [C4!41!_g_40!hide],NULL as [C4!41!],NULL as [C5!42!_g_41!hide],NULL as [C5!42!],NULL as [C6!43!_g_42!hide],NULL as [C6!43!],NULL as [C7!44!_g_43!hide],NULL as [C7!44!],NULL as [C8!45!_g_44!hide],NULL as [C8!45!] union all select 2,1,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 3,2,0,_Q2._TBIJBDMAHA,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 4,3,0,_Q2._TBIJBDMAHA,2,3,_Q2._TBIGOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 5,3,0,_Q2._TBIJBDMAHA,2,NULL,NULL,4,_Q2._TBIHOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 6,3,0,_Q2._TBIJBDMAHA,2,NULL,NULL,NULL,NULL,5,_Q2._TBIIOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 7,2,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 8,7,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,7,_Q2._TBIJOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 9,7,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,8,_Q2._TBIKOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 10,7,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,9,_Q2._TBAMKPFNEA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 11,7,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,10,_Q2._TBIMOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 12,7,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,11,_Q2._TBINOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 13,2,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 14,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,13,_Q2._TBIGOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 15,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,14,_Q2._TBIHOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 16,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,15,_Q2._TBIIOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 17,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,16,_Q2._TBIJOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 18,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,17,_Q2._TBIKOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 19,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,18,_Q2._TBAMKPFNEA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 20,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,19,_Q2._TBIMOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 21,13,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,20,_Q2._TBINOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 22,2,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 23,22,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,_Q7._TCIGOCMAHA,_Q7._TCIHOCMAHA,_Q7._TCIIOCMAHA,_Q7._TCIJOCMAHA,_Q7._TCIKOCMAHA,_Q7._TCAMKPFNEA,_Q7._TCIMOCMAHA,_Q7._TCIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 24,23,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,23,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 25,24,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,23,24,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 26,25,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,23,24,25,N'false',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 27,23,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,NULL,NULL,NULL,NULL,26,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 28,27,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,NULL,NULL,NULL,NULL,26,27,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 29,28,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q7._TCIJBDMAHA,NULL,NULL,NULL,NULL,26,27,28,N'true',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TCINOCMAHA,_QB0.C7 AS _TCIMOCMAHA,_QB0.C6 AS _TCAMKPFNEA,_QB0.C5 AS _TCIKOCMAHA,_QB0.C4 AS _TCIJOCMAHA,_QB0.C3 AS _TCIIOCMAHA,_QB0.C2 AS _TCIHOCMAHA,_QB0.C1 AS _TCIGOCMAHA,_QB0.Guid AS _TCIJBDMAHA,_QB0.Table1Guid AS _TCILOCMAHA from Table_2 _QB0) _Q7,(select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 where _Q2._TBIJBDMAHA=_Q7._TCILOCMAHA union all select 30,2,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,29,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 31,30,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,29,30,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 32,31,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,29,30,31,N'false',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 33,2,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,32,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 34,33,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,32,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 35,34,0,_Q2._TBIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,32,33,34,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TBINOCMAHA,_QB0.C7 AS _TBIMOCMAHA,_QB0.C6 AS _TBAMKPFNEA,_QB0.C5 AS _TBIKOCMAHA,_QB0.C4 AS _TBIJOCMAHA,_QB0.C3 AS _TBIIOCMAHA,_QB0.C2 AS _TBIHOCMAHA,_QB0.C1 AS _TBIGOCMAHA,_QB0.Guid AS _TBIJBDMAHA from Table_1 _QB0) _Q2 union all select 36,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 37,36,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 38,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,37,_Q20._TDIGOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 39,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,38,_Q20._TDIHOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 40,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,39,_Q20._TDIIOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 41,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,40,_Q20._TDIJOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 42,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,41,_Q20._TDIKOCMAHA,NULL,NULL,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 43,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,42,_Q20._TDAMKPFNEA,NULL,NULL,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 44,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,43,_Q20._TDIMOCMAHA,NULL,NULL from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 union all select 45,37,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,_Q20._TDIJBDMAHA,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,44,_Q20._TDINOCMAHA from (select _QB0.C8 AS _TDINOCMAHA,_QB0.C7 AS _TDIMOCMAHA,_QB0.C6 AS _TDAMKPFNEA,_QB0.C5 AS _TDIKOCMAHA,_QB0.C4 AS _TDIJOCMAHA,_QB0.C3 AS _TDIIOCMAHA,_QB0.C2 AS _TDIHOCMAHA,_QB0.C1 AS _TDIGOCMAHA,_QB0.Guid AS _TDIJBDMAHA,_QB0.Table1Guid AS _TDILOCMAHA from Table_2 _QB0) _Q20 order by 3,64,65,80,78,76,74,72,70,68,66,4,61,62,63,57,58,59,40,48,53,54,55,49,50,51,23,38,36,34,32,30,28,26,24,12,21,19,17,15,13,5,10,8,6,2,1 for xml explicit, binary base64


    Thursday, April 12, 2012 6:17 PM

Answers

  • Re your co-developers, developing a solution that they can manage it sounds like the tail wagging the dog, still it's up to you ; )

    You might consider raising a connect item (Microsoft bug report) around the performance of your SQLXML / xsd solution, see what they say:

    connect.microsoft.com

    Let the group know how you get on.
    • Edited by wBobEditor Saturday, April 14, 2012 1:42 PM
    • Marked as answer by KJian_ Thursday, April 19, 2012 5:53 AM
    Saturday, April 14, 2012 1:41 PM

All replies

  • As an alternative, have you tried coding up the FOR XML PATH version of the equivalent query and calling it directly from the database for comparison?  (Assuming you are using SQL 2005 or above).

    Friday, April 13, 2012 10:37 AM
  • This worked for me and might be a valid alternative, ie create views instead of XSDs:

    USE tempdb
    GO
    
    IF OBJECT_ID('tempdb.[dbo].[Table_2]') IS NOT NULL DROP TABLE [dbo].[Table_2]
    GO
    IF OBJECT_ID('tempdb.[dbo].[Table_1]') IS NOT NULL DROP TABLE [dbo].[Table_1]
    GO
    
    CREATE TABLE [dbo].[Table_1](
    	[Guid] [uniqueidentifier] NOT NULL,
    	[C1] [nchar](10) NULL,
    	[C2] [nchar](10) NULL,
    	[C3] [nchar](10) NULL,
    	[C4] [nchar](10) NULL,
    	[C5] [nchar](10) NULL,
    	[C6] [nchar](10) NULL,
    	[C7] [nchar](10) NULL,
    	[C8] [nchar](10) NULL,
     CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED 
    (
    	[Guid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    
    CREATE TABLE [dbo].[Table_2](
    	[Guid] [uniqueidentifier] NOT NULL,
    	[Table1Guid] [uniqueidentifier] NOT NULL,
    	[C1] [nchar](10) NULL,
    	[C2] [nchar](10) NULL,
    	[C3] [nchar](10) NULL,
    	[C4] [nchar](10) NULL,
    	[C5] [nchar](10) NULL,
    	[C6] [nchar](10) NULL,
    	[C7] [nchar](10) NULL,
    	[C8] [nchar](10) NULL,
     CONSTRAINT [PK_Table_2_1] PRIMARY KEY CLUSTERED 
    (
    	[Guid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Table_2]  WITH CHECK ADD  CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Table1Guid])
    REFERENCES [dbo].[Table_1] ([Guid])
    GO
    
    -- Dummy data
    INSERT INTO [dbo].[Table_1] ( [Guid], C1, C2, C3, C4, C5, C6, C7, C8 )
    VALUES ( NEWID(), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ) )
    
    INSERT INTO [dbo].[Table_2] ( [Guid], [Table1Guid], C1, C2, C3, C4, C5, C6, C7, C8 )
    SELECT NEWID(), [Guid], LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 ), LEFT( NEWID(), 10 )
    FROM [dbo].[Table_1] 
    GO
    
    
    SELECT
    	(
    	SELECT
    	(
    	SELECT
    		C1,
    		C2,
    		C3
    	FOR XML PATH(''), TYPE
    	) "C1-C3-Columns",
    	(
    	SELECT
    		C4, 
    		C5,
    		C6,
    		C7, 
    		C8
    	FOR XML PATH(''), TYPE
    	) "C4-8-Columns", -- !!should be C4-C8?
    	(
    	SELECT
    		C1,
    		C2,
    		C3,
    		C4,
    		C5,
    		C6,
    		C7,
    		C8
    	FOR XML PATH(''), TYPE
    	) "C1-8-Columns",
    	(
    	SELECT
    		C1,
    		C2,
    		C3,
    		C4,
    		C5,
    		C6,
    		C7,
    		--C8,	!!this is missing in original query
    		'false' AS "AllVirtualData/VirtualData/SomeBool",
    		'true' AS "AllSkipDescriptions/SkipDescription/Skip"
    	FROM dbo.Table_2 t2 
    	WHERE t1.[Guid] = t2.Table1Guid
    	FOR XML PATH(''), TYPE
    	) "Table_2_Data/Table_2",
    	'false' AS "AllAlternateDimensions/AlternateDimension/UseAlternateDimension",
    	'' AS "AllSkipDescriptions/SkipDescription/Skip"
    	FOR XML PATH(''), TYPE
    	) AS "Table_1",
    	(
    	SELECT
    		C1,
    		C2,
    		C3,
    		C4,
    		C5,
    		C6,
    		C7,
    		C8
    	FROM dbo.Table_2 t2 
    	WHERE t1.[Guid] = t2.Table1Guid
    	FOR XML PATH(''), TYPE
    	) "Table2Dump/C1-8-Columns"
    FROM dbo.Table_1 t1
    FOR XML PATH('InnerDocument'), TYPE

    Friday, April 13, 2012 11:45 AM
  • wBob, I appreciate your replies and the time you spent formulating some suggestions.  Unfortunately these suggestions mean to abandon using SQLXML with annotated XSDs.

    I can definitely write custom SQL in a stored procedure or a variety of views in the database to produce the similar result, however the flexibility and indirection that SQLXML with XSDs provide is very appealing.  Its also worth mentioning that my co-developers that could be responsible for this layer of translation may not have an strong grasp of TSQL, so using the XSD is a fantastic abstraction....(BUT IT'S SLOW!!!!)

    Can anyone else speak up about SQLXML with annotated XSDs in a production environment and how they have dealt with performance issues?  Is this just a "cute" technology that Microsoft has developed and only worthwhile for returning simple structures as in their examples (e.g. Customers and Orders).  Someone from Microsoft please speak up.

    Friday, April 13, 2012 4:24 PM
  • Re your co-developers, developing a solution that they can manage it sounds like the tail wagging the dog, still it's up to you ; )

    You might consider raising a connect item (Microsoft bug report) around the performance of your SQLXML / xsd solution, see what they say:

    connect.microsoft.com

    Let the group know how you get on.
    • Edited by wBobEditor Saturday, April 14, 2012 1:42 PM
    • Marked as answer by KJian_ Thursday, April 19, 2012 5:53 AM
    Saturday, April 14, 2012 1:41 PM
  • Hello everyone,

    Please see Microsoft's official reply from my Connect bug that I filed four months ago.

    http://connect.microsoft.com/SQLServer/feedback/details/737134/sqlxml-annotated-xsd-queries-are-extremely-inneficient

    Their response is that the SQLXML library is no longer under development.  This is very unfortunate as I have found it to be a very powerful tool for producing XML from a schema without having to write reams of FOR XML tsql code.

    Given their response, I would like to try and petition them to open source the SQLXML library.  Please weigh in on this if you support opening up this library so we can improve the performance of it.

    Wednesday, September 05, 2012 6:12 PM
  • Disappointing I know, but at least you got a straight answer, rather than a "will fix for SQL 2032" or "As Designed".

    If you do want to consider the workaround ( ie FOR XML RAW/PATH/AUTO queries ) then at least you know there are people on this and other forums willing to help.

    Good luck!

    Thursday, September 06, 2012 10:22 AM
  • What is your feeling about petitioning Microsoft to open source the SQLXML library?  They have gone this route with Entity Framework.  I must reiterate that this library, which includes the annotated schema feature is a real sleeper as far as their developer tools go.  If more developers knew about it, I'm sure we could get some momentum going.
    Thursday, September 06, 2012 4:09 PM
  • Honestly I would say it's a waste of time.  SQLXML, whilst powerful when working properly is an old COM component.  It has some problems; it's single-threaded, it can get confused when doing bulk loads to tables with foreign keys, it doesn't really support foreign key generation for anything other than ints.  You yourself have encountered serious performance problems.  A second is an age in modern programming terms.  30 seconds is an aeon.

    Maybe it's time to take a step back, forgot about technologies, work out exactly what you want to do and try and find a good modern solution.  This may end up being anyone one of FOR XML, SQLCLR, .net, SSIS, Biztalk, a combination or something else entirely.

    If you want to get a wider view on this, I'd suggest posting something on the #sqlhelp tag on Twitter - see what the twitterverse thinks.  There's a good guy Bob Beauchemin ( @bobbeauch ) who might be able to help.

    Thursday, September 06, 2012 9:06 PM
  • Hi wBob,

    You're helping me with my argument to make it open source :-)  The problems you mentioned need fixing, and I'm sure the community at large can improve the product.

    Friday, September 07, 2012 2:58 PM
  • COM is a bit old-fashioned though.  It would be like making DOS open-source; interesting from an academic point of view, or for people with too much time on their hands.  As mentioned, maybe there is another way to do what you want to do; what was that again?  ; )
    Friday, September 07, 2012 3:24 PM
  • :-)

    I'm making full use of the annotated xml schema feature of the SQLXML library to produce an xml document from an xml schema.

    E.g.

    http://msdn.microsoft.com/en-us/library/ms171870

    I have a strong feeling that this is a forgotten tool that someone at Microsoft put together years ago and is no longer with the company.

    Are you aware of any other library that supports the same functionality (e.g. define an XML schema that will produce an XML document from the database).

    Friday, September 07, 2012 3:28 PM
  • I think Biztalk can do this type of thing although I don't know a lot about the product.  LINQ maybe ...
    • Edited by wBobEditor Friday, September 07, 2012 3:43 PM
    Friday, September 07, 2012 3:31 PM