"Don Miller" wrote:

> I guess I don't understand XML EXPLICIT or don't know how to go more than a
> level or two deep. What I want as output is below (along with my query
> attempt below it). Basically, what I get is something like:
>
> <array>
>    <dict>
>       <key>Letter</key>
>       <string>S</string>
>       <string>T</string>
>       <string>U</string>
>      ...
>
> So, what am I doing wrong? (and also, how would I order the Letters?)
>
> Thanks for any tips.
>
>
> DESIRED OUTPUT
>
> <array>
>    <dict>
>       <key>Letter</key>
>       <string>S</string>
>       <key>Customers</key>
>       <array>
>          <dict>
>             <key>Name</key>
>             <string>Shore Company</string>
>             <key>ID</key>
>             <integer>2776</integer>
>          </dict>
>          <dict>
>             <key>Name</key>
>             <string>Szabo</string>
>             <key>ID</key>
>             <integer>1408</integer>
>          </dict>
>       </array>
>    </dict>
>    <dict>
>       <key>Letter</key>
>       <string>Z</string>
>       <key>Customers</key>
>       <array>
>          <dict>
>             <key>Name</key>
>             <string>Zeba Manufacturing</string>
>             <key>ID</key>
>             <integer>280</integer>
>          </dict>
>          <dict>
>             <key>Name</key>
>             <string>Zorba</string>
>             <key>ID</key>
>             <integer>394</integer>
>          </dict>
>       </array>
>    </dict>
>    ...
> </array>
>
> QUERY THAT DOES NOT WORK
>
> USE NORTHWIND
>
>     SELECT  1 as Tag, NULL as Parent,
>
>         null as [array!1], -- parent array
>         null as [dict!2], -- child dict
>         null as [key!3], --  dict Letter key
>         null as [string!4], -- letter value
>         null as [key!5], -- Customers key
>         null as [array!6], -- array child of dict
>         null as [dict!7], -- dict child of array
>         null as [key!8], -- name key child of dict
>         null as [string!9], -- name value (child of dict)
>         null as [key!10],  -- id key
>         null as [integer!11] -- id value
>
> UNION ALL
>
>     SELECT     2 as Tag, 1 as Parent,
>
>         null, -- parent array
>         null, -- child dict *****
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT     3 as Tag, 2 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         'Letter', --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         left(CompanyName, 1), -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null
>
>         FROM Customers
>
>
>
> UNION ALL
>
>     SELECT     5 as Tag, 2 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         'Customers', -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT     6 as Tag, 2 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict  ****
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT     7 as Tag, 6 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array   ***
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT     8 as Tag, 7 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         'Name', -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
>
> UNION ALL
>
>     SELECT     9 as Tag, 7 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         CompanyName, -- name value (child of dict)
>         null,  -- id key
>         null -- id value
>
>         FROM Customers
>
>
>
> UNION ALL
>
>     SELECT     10 as Tag, 7 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         'ID',  -- id key
>         null -- id value
>
> UNION ALL
>
>     SELECT     11 as Tag, 7 as Parent,
>
>         null, -- parent array
>         null, -- child dict
>         null, --  dict Letter key
>         null, -- letter value
>         null, -- Customers key
>         null, -- array child of dict
>         null, -- dict child of array
>         null, -- name key child of dict
>         null, -- name value (child of dict)
>         null,  -- id key
>         CustomerID -- id value
>
>         FROM Customers
>
> FOR XML EXPLICIT
>
>
>
>

"Bob" <B**@discussions.microsoft.com> wrote in message
news:FCE502F9-D5EB-4C3F-9F7C-B9B885B341A5@microsoft.com...
> Are you using SQL 2000 or 2005?
>
> "Don Miller" wrote:
>
>> I guess I don't understand XML EXPLICIT or don't know how to go more than
>> a
>> level or two deep. What I want as output is below (along with my query
>> attempt below it). Basically, what I get is something like:
>>
>> <array>
>>    <dict>
>>       <key>Letter</key>
>>       <string>S</string>
>>       <string>T</string>
>>       <string>U</string>
>>      ...
>>
>> So, what am I doing wrong? (and also, how would I order the Letters?)
>>
>> Thanks for any tips.
>>
>>
>> DESIRED OUTPUT
>>
>> <array>
>>    <dict>
>>       <key>Letter</key>
>>       <string>S</string>
>>       <key>Customers</key>
>>       <array>
>>          <dict>
>>             <key>Name</key>
>>             <string>Shore Company</string>
>>             <key>ID</key>
>>             <integer>2776</integer>
>>          </dict>
>>          <dict>
>>             <key>Name</key>
>>             <string>Szabo</string>
>>             <key>ID</key>
>>             <integer>1408</integer>
>>          </dict>
>>       </array>
>>    </dict>
>>    <dict>
>>       <key>Letter</key>
>>       <string>Z</string>
>>       <key>Customers</key>
>>       <array>
>>          <dict>
>>             <key>Name</key>
>>             <string>Zeba Manufacturing</string>
>>             <key>ID</key>
>>             <integer>280</integer>
>>          </dict>
>>          <dict>
>>             <key>Name</key>
>>             <string>Zorba</string>
>>             <key>ID</key>
>>             <integer>394</integer>
>>          </dict>
>>       </array>
>>    </dict>
>>    ...
>> </array>
>>
>> QUERY THAT DOES NOT WORK
>>
>> USE NORTHWIND
>>
>>     SELECT  1 as Tag, NULL as Parent,
>>
>>         null as [array!1], -- parent array
>>         null as [dict!2], -- child dict
>>         null as [key!3], --  dict Letter key
>>         null as [string!4], -- letter value
>>         null as [key!5], -- Customers key
>>         null as [array!6], -- array child of dict
>>         null as [dict!7], -- dict child of array
>>         null as [key!8], -- name key child of dict
>>         null as [string!9], -- name value (child of dict)
>>         null as [key!10],  -- id key
>>         null as [integer!11] -- id value
>>
>> UNION ALL
>>
>>     SELECT     2 as Tag, 1 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict *****
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT     3 as Tag, 2 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         'Letter', --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         left(CompanyName, 1), -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null
>>
>>         FROM Customers
>>
>>
>>
>> UNION ALL
>>
>>     SELECT     5 as Tag, 2 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         'Customers', -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT     6 as Tag, 2 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict  ****
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT     7 as Tag, 6 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array   ***
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT     8 as Tag, 7 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         'Name', -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>>
>> UNION ALL
>>
>>     SELECT     9 as Tag, 7 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         CompanyName, -- name value (child of dict)
>>         null,  -- id key
>>         null -- id value
>>
>>         FROM Customers
>>
>>
>>
>> UNION ALL
>>
>>     SELECT     10 as Tag, 7 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         'ID',  -- id key
>>         null -- id value
>>
>> UNION ALL
>>
>>     SELECT     11 as Tag, 7 as Parent,
>>
>>         null, -- parent array
>>         null, -- child dict
>>         null, --  dict Letter key
>>         null, -- letter value
>>         null, -- Customers key
>>         null, -- array child of dict
>>         null, -- dict child of array
>>         null, -- name key child of dict
>>         null, -- name value (child of dict)
>>         null,  -- id key
>>         CustomerID -- id value
>>
>>         FROM Customers
>>
>> FOR XML EXPLICIT
>>
>>
>>
>>

"Don Miller" <nospam@nospam.com> wrote in message
news:eqfk%23JFnJHA.5520@TK2MSFTNGP06.phx.gbl...
> Both SQL 2000 and 2005 (most of my customers migrated to 2005 though).
> After spending all day on this (I'm must be an imbecile) if there is an
> easier way with 2005, I'm open.
>
> I've come closer to the goal when I used the element directive although
> never right.
>
>    SELECT   1 as Tag, NULL as Parent,
>
>        null as [array!1], -- parent array
>        null as [dict!2!key!element],
>        null as [dict!2!string!element],
>        null as [dict!2!key!element],
>        null as [array!3!dict!element],
>        null as [dict!4!key!element],
>        null as [dict!4!string!element],
>        null as [dict!4!key!element],
>        null as [dict!4!integer!element]
>
> UNION ALL
>
>    SELECT  Distinct 2 as Tag, 1 as Parent,
>
>        null, -- parent array
>        'Letter', -- child dict
>        left(CompanyName, 1), --  dict Letter key
>        'Customers', -- letter value
>       null,
>       null,
>       null,
>       null,
>       null
>
> FROM Customers
>
> UNION ALL
>
>    SELECT  Distinct 3 as Tag, 2 as Parent,
>
>        null, -- parent array
>        'Letter', -- child dict
>        left(CompanyName, 1), --  dict Letter key
>        'Customers', -- letter value
>       null,
>       null,
>       null,
>       null,
>       null
>
> FROM Customers
>
> UNION ALL
>
>    SELECT  Distinct 4 as Tag, 3 as Parent,
>
>        null, -- parent array
>        'Letter', -- child dict
>        left(CompanyName, 1), --  dict Letter key
>        'Customers', -- letter value
>       null,
>       'Name',
>      CompanyName,
>     ' ID',
>     CustomerID
>
> FROM Customers
>
> order by [dict!2!string!element]
>
> FOR XML EXPLICIT
>
>
>
> "Bob" <B**@discussions.microsoft.com> wrote in message
> news:FCE502F9-D5EB-4C3F-9F7C-B9B885B341A5@microsoft.com...
>> Are you using SQL 2000 or 2005?
>>
>> "Don Miller" wrote:
>>
>>> I guess I don't understand XML EXPLICIT or don't know how to go more
>>> than a
>>> level or two deep. What I want as output is below (along with my query
>>> attempt below it). Basically, what I get is something like:
>>>
>>> <array>
>>>    <dict>
>>>       <key>Letter</key>
>>>       <string>S</string>
>>>       <string>T</string>
>>>       <string>U</string>
>>>      ...
>>>
>>> So, what am I doing wrong? (and also, how would I order the Letters?)
>>>
>>> Thanks for any tips.
>>>
>>>
>>> DESIRED OUTPUT
>>>
>>> <array>
>>>    <dict>
>>>       <key>Letter</key>
>>>       <string>S</string>
>>>       <key>Customers</key>
>>>       <array>
>>>          <dict>
>>>             <key>Name</key>
>>>             <string>Shore Company</string>
>>>             <key>ID</key>
>>>             <integer>2776</integer>
>>>          </dict>
>>>          <dict>
>>>             <key>Name</key>
>>>             <string>Szabo</string>
>>>             <key>ID</key>
>>>             <integer>1408</integer>
>>>          </dict>
>>>       </array>
>>>    </dict>
>>>    <dict>
>>>       <key>Letter</key>
>>>       <string>Z</string>
>>>       <key>Customers</key>
>>>       <array>
>>>          <dict>
>>>             <key>Name</key>
>>>             <string>Zeba Manufacturing</string>
>>>             <key>ID</key>
>>>             <integer>280</integer>
>>>          </dict>
>>>          <dict>
>>>             <key>Name</key>
>>>             <string>Zorba</string>
>>>             <key>ID</key>
>>>             <integer>394</integer>
>>>          </dict>
>>>       </array>
>>>    </dict>
>>>    ...
>>> </array>
>>>
>>> QUERY THAT DOES NOT WORK
>>>
>>> USE NORTHWIND
>>>
>>>     SELECT  1 as Tag, NULL as Parent,
>>>
>>>         null as [array!1], -- parent array
>>>         null as [dict!2], -- child dict
>>>         null as [key!3], --  dict Letter key
>>>         null as [string!4], -- letter value
>>>         null as [key!5], -- Customers key
>>>         null as [array!6], -- array child of dict
>>>         null as [dict!7], -- dict child of array
>>>         null as [key!8], -- name key child of dict
>>>         null as [string!9], -- name value (child of dict)
>>>         null as [key!10],  -- id key
>>>         null as [integer!11] -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     2 as Tag, 1 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict *****
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     3 as Tag, 2 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         'Letter', --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         left(CompanyName, 1), -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null
>>>
>>>         FROM Customers
>>>
>>>
>>>
>>> UNION ALL
>>>
>>>     SELECT     5 as Tag, 2 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         'Customers', -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     6 as Tag, 2 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict  ****
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     7 as Tag, 6 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array   ***
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     8 as Tag, 7 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         'Name', -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>>
>>> UNION ALL
>>>
>>>     SELECT     9 as Tag, 7 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         CompanyName, -- name value (child of dict)
>>>         null,  -- id key
>>>         null -- id value
>>>
>>>         FROM Customers
>>>
>>>
>>>
>>> UNION ALL
>>>
>>>     SELECT     10 as Tag, 7 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         'ID',  -- id key
>>>         null -- id value
>>>
>>> UNION ALL
>>>
>>>     SELECT     11 as Tag, 7 as Parent,
>>>
>>>         null, -- parent array
>>>         null, -- child dict
>>>         null, --  dict Letter key
>>>         null, -- letter value
>>>         null, -- Customers key
>>>         null, -- array child of dict
>>>         null, -- dict child of array
>>>         null, -- name key child of dict
>>>         null, -- name value (child of dict)
>>>         null,  -- id key
>>>         CustomerID -- id value
>>>
>>>         FROM Customers
>>>
>>> FOR XML EXPLICIT
>>>
>>>
>>>
>>>
>
>

"Don Miller" wrote:

> Both SQL 2000 and 2005 (most of my customers migrated to 2005 though). After
> spending all day on this (I'm must be an imbecile) if there is an easier way
> with 2005, I'm open.
>
> I've come closer to the goal when I used the element directive although
> never right.
>
>     SELECT   1 as Tag, NULL as Parent,
>
>         null as [array!1], -- parent array
>         null as [dict!2!key!element],
>         null as [dict!2!string!element],
>         null as [dict!2!key!element],
>         null as [array!3!dict!element],
>         null as [dict!4!key!element],
>         null as [dict!4!string!element],
>         null as [dict!4!key!element],
>         null as [dict!4!integer!element]
>
> UNION ALL
>
>     SELECT  Distinct 2 as Tag, 1 as Parent,
>
>         null, -- parent array
>         'Letter', -- child dict
>         left(CompanyName, 1), --  dict Letter key
>         'Customers', -- letter value
>        null,
>        null,
>        null,
>        null,
>        null
>
>  FROM Customers
>
> UNION ALL
>
>     SELECT  Distinct 3 as Tag, 2 as Parent,
>
>         null, -- parent array
>         'Letter', -- child dict
>         left(CompanyName, 1), --  dict Letter key
>         'Customers', -- letter value
>        null,
>        null,
>        null,
>        null,
>        null
>
>  FROM Customers
>
> UNION ALL
>
>     SELECT  Distinct 4 as Tag, 3 as Parent,
>
>         null, -- parent array
>         'Letter', -- child dict
>         left(CompanyName, 1), --  dict Letter key
>         'Customers', -- letter value
>        null,
>        'Name',
>       CompanyName,
>      ' ID',
>      CustomerID
>
>  FROM Customers
>
> order by [dict!2!string!element]
>
> FOR XML EXPLICIT
>
>
>
> "Bob" <B**@discussions.microsoft.com> wrote in message
> news:FCE502F9-D5EB-4C3F-9F7C-B9B885B341A5@microsoft.com...
> > Are you using SQL 2000 or 2005?
> >
> > "Don Miller" wrote:
> >
> >> I guess I don't understand XML EXPLICIT or don't know how to go more than
> >> a
> >> level or two deep. What I want as output is below (along with my query
> >> attempt below it). Basically, what I get is something like:
> >>
> >> <array>
> >>    <dict>
> >>       <key>Letter</key>
> >>       <string>S</string>
> >>       <string>T</string>
> >>       <string>U</string>
> >>      ...
> >>
> >> So, what am I doing wrong? (and also, how would I order the Letters?)
> >>
> >> Thanks for any tips.
> >>
> >>
> >> DESIRED OUTPUT
> >>
> >> <array>
> >>    <dict>
> >>       <key>Letter</key>
> >>       <string>S</string>
> >>       <key>Customers</key>
> >>       <array>
> >>          <dict>
> >>             <key>Name</key>
> >>             <string>Shore Company</string>
> >>             <key>ID</key>
> >>             <integer>2776</integer>
> >>          </dict>
> >>          <dict>
> >>             <key>Name</key>
> >>             <string>Szabo</string>
> >>             <key>ID</key>
> >>             <integer>1408</integer>
> >>          </dict>
> >>       </array>
> >>    </dict>
> >>    <dict>
> >>       <key>Letter</key>
> >>       <string>Z</string>
> >>       <key>Customers</key>
> >>       <array>
> >>          <dict>
> >>             <key>Name</key>
> >>             <string>Zeba Manufacturing</string>
> >>             <key>ID</key>
> >>             <integer>280</integer>
> >>          </dict>
> >>          <dict>
> >>             <key>Name</key>
> >>             <string>Zorba</string>
> >>             <key>ID</key>
> >>             <integer>394</integer>
> >>          </dict>
> >>       </array>
> >>    </dict>
> >>    ...
> >> </array>
> >>
> >> QUERY THAT DOES NOT WORK
> >>
> >> USE NORTHWIND
> >>
> >>     SELECT  1 as Tag, NULL as Parent,
> >>
> >>         null as [array!1], -- parent array
> >>         null as [dict!2], -- child dict
> >>         null as [key!3], --  dict Letter key
> >>         null as [string!4], -- letter value
> >>         null as [key!5], -- Customers key
> >>         null as [array!6], -- array child of dict
> >>         null as [dict!7], -- dict child of array
> >>         null as [key!8], -- name key child of dict
> >>         null as [string!9], -- name value (child of dict)
> >>         null as [key!10],  -- id key
> >>         null as [integer!11] -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT     2 as Tag, 1 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict *****
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT     3 as Tag, 2 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         'Letter', --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         left(CompanyName, 1), -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null
> >>
> >>         FROM Customers
> >>
> >>
> >>
> >> UNION ALL
> >>
> >>     SELECT     5 as Tag, 2 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         'Customers', -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT     6 as Tag, 2 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict  ****
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT     7 as Tag, 6 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array   ***
> >>         null, -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >> UNION ALL
> >>
> >>     SELECT     8 as Tag, 7 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         'Name', -- name key child of dict
> >>         null, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >>
> >> UNION ALL
> >>
> >>     SELECT     9 as Tag, 7 as Parent,
> >>
> >>         null, -- parent array
> >>         null, -- child dict
> >>         null, --  dict Letter key
> >>         null, -- letter value
> >>         null, -- Customers key
> >>         null, -- array child of dict
> >>         null, -- dict child of array
> >>         null, -- name key child of dict
> >>         CompanyName, -- name value (child of dict)
> >>         null,  -- id key
> >>         null -- id value
> >>
> >>         FROM Customers
> >>
> >>
> >>
> >> UNION ALL
> >>
> >>     SELECT     10 as Tag, 7 as Parent,
> >>

"Bob" <B**@discussions.microsoft.com> wrote in message
news:F8017C37-EE8F-4858-A4B6-4EDA6F296E57@microsoft.com...
> Not sure exactly what your customer table looks like, but here's a demo
> with
> SQL 2005 FOR XML PATH:
>
> USE tempdb
> GO
>
> CREATE TABLE #customers ( CustomerID INT PRIMARY KEY, CompanyName
> VARCHAR(50) UNIQUE NOT NULL  )
> GO
>
> INSERT INTO #customers VALUES ( 2776, 'Shore Company' )
> INSERT INTO #customers VALUES ( 1408, 'Szabo' )
> INSERT INTO #customers VALUES ( 280, 'Zeba Manufacturing' )
> INSERT INTO #customers VALUES ( 394, 'Zorba' )
> GO
>
> SELECT
> 'Letter' AS "dict/key",
> Letter AS "dict/string",
> 'Customers' AS "dict/key",
> (
> SELECT
> 'Name' AS "dict/key",
> CompanyName AS "dict/string",
> 'ID' AS "dict/key",
> CustomerID AS "dict/integer"
> FROM
> (
> SELECT CompanyName, CustomerID
> FROM #customers c
> WHERE letters.Letter = LEFT( CompanyName, 1 )
> ) x
> ORDER BY CompanyName
> FOR XML PATH(''), ROOT('array'), TYPE
> ) AS "dict"
> FROM
> (
> SELECT DISTINCT LEFT( CompanyName, 1 ) AS Letter
> FROM #customers
> ) letters
> ORDER BY letters.Letter
> FOR XML PATH(''), ROOT('array')
>
> HTH
> wBob
> Rate the post
>
> "Don Miller" wrote:
>
>> Both SQL 2000 and 2005 (most of my customers migrated to 2005 though).
>> After
>> spending all day on this (I'm must be an imbecile) if there is an easier
>> way
>> with 2005, I'm open.
>>
>> I've come closer to the goal when I used the element directive although
>> never right.
>>
>>     SELECT   1 as Tag, NULL as Parent,
>>
>>         null as [array!1], -- parent array
>>         null as [dict!2!key!element],
>>         null as [dict!2!string!element],
>>         null as [dict!2!key!element],
>>         null as [array!3!dict!element],
>>         null as [dict!4!key!element],
>>         null as [dict!4!string!element],
>>         null as [dict!4!key!element],
>>         null as [dict!4!integer!element]
>>
>> UNION ALL
>>
>>     SELECT  Distinct 2 as Tag, 1 as Parent,
>>
>>         null, -- parent array
>>         'Letter', -- child dict
>>         left(CompanyName, 1), --  dict Letter key
>>         'Customers', -- letter value
>>        null,
>>        null,
>>        null,
>>        null,
>>        null
>>
>>  FROM Customers
>>
>> UNION ALL
>>
>>     SELECT  Distinct 3 as Tag, 2 as Parent,
>>
>>         null, -- parent array
>>         'Letter', -- child dict
>>         left(CompanyName, 1), --  dict Letter key
>>         'Customers', -- letter value
>>        null,
>>        null,
>>        null,
>>        null,
>>        null
>>
>>  FROM Customers
>>
>> UNION ALL
>>
>>     SELECT  Distinct 4 as Tag, 3 as Parent,
>>
>>         null, -- parent array
>>         'Letter', -- child dict
>>         left(CompanyName, 1), --  dict Letter key
>>         'Customers', -- letter value
>>        null,
>>        'Name',
>>       CompanyName,
>>      ' ID',
>>      CustomerID
>>
>>  FROM Customers
>>
>> order by [dict!2!string!element]
>>
>> FOR XML EXPLICIT
>>
>>
>>
>> "Bob" <B**@discussions.microsoft.com> wrote in message
>> news:FCE502F9-D5EB-4C3F-9F7C-B9B885B341A5@microsoft.com...
>> > Are you using SQL 2000 or 2005?
>> >
>> > "Don Miller" wrote:
>> >
>> >> I guess I don't understand XML EXPLICIT or don't know how to go more
>> >> than
>> >> a
>> >> level or two deep. What I want as output is below (along with my query
>> >> attempt below it). Basically, what I get is something like:
>> >>
>> >> <array>
>> >>    <dict>
>> >>       <key>Letter</key>
>> >>       <string>S</string>
>> >>       <string>T</string>
>> >>       <string>U</string>
>> >>      ...
>> >>
>> >> So, what am I doing wrong? (and also, how would I order the Letters?)
>> >>
>> >> Thanks for any tips.
>> >>
>> >>
>> >> DESIRED OUTPUT
>> >>
>> >> <array>
>> >>    <dict>
>> >>       <key>Letter</key>
>> >>       <string>S</string>
>> >>       <key>Customers</key>
>> >>       <array>
>> >>          <dict>
>> >>             <key>Name</key>
>> >>             <string>Shore Company</string>
>> >>             <key>ID</key>
>> >>             <integer>2776</integer>
>> >>          </dict>
>> >>          <dict>
>> >>             <key>Name</key>
>> >>             <string>Szabo</string>
>> >>             <key>ID</key>
>> >>             <integer>1408</integer>
>> >>          </dict>
>> >>       </array>
>> >>    </dict>
>> >>    <dict>
>> >>       <key>Letter</key>
>> >>       <string>Z</string>
>> >>       <key>Customers</key>
>> >>       <array>
>> >>          <dict>
>> >>             <key>Name</key>
>> >>             <string>Zeba Manufacturing</string>
>> >>             <key>ID</key>
>> >>             <integer>280</integer>
>> >>          </dict>
>> >>          <dict>
>> >>             <key>Name</key>
>> >>             <string>Zorba</string>
>> >>             <key>ID</key>
>> >>             <integer>394</integer>
>> >>          </dict>
>> >>       </array>
>> >>    </dict>
>> >>    ...
>> >> </array>
>> >>
>> >> QUERY THAT DOES NOT WORK
>> >>
>> >> USE NORTHWIND
>> >>
>> >>     SELECT  1 as Tag, NULL as Parent,
>> >>
>> >>         null as [array!1], -- parent array
>> >>         null as [dict!2], -- child dict
>> >>         null as [key!3], --  dict Letter key
>> >>         null as [string!4], -- letter value
>> >>         null as [key!5], -- Customers key
>> >>         null as [array!6], -- array child of dict
>> >>         null as [dict!7], -- dict child of array
>> >>         null as [key!8], -- name key child of dict
>> >>         null as [string!9], -- name value (child of dict)
>> >>         null as [key!10],  -- id key
>> >>         null as [integer!11] -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     2 as Tag, 1 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict *****
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     3 as Tag, 2 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         'Letter', --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         left(CompanyName, 1), -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null
>> >>
>> >>         FROM Customers
>> >>
>> >>
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     5 as Tag, 2 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         'Customers', -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     6 as Tag, 2 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict  ****
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     7 as Tag, 6 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array   ***
>> >>         null, -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     8 as Tag, 7 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         'Name', -- name key child of dict
>> >>         null, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     9 as Tag, 7 as Parent,
>> >>
>> >>         null, -- parent array
>> >>         null, -- child dict
>> >>         null, --  dict Letter key
>> >>         null, -- letter value
>> >>         null, -- Customers key
>> >>         null, -- array child of dict
>> >>         null, -- dict child of array
>> >>         null, -- name key child of dict
>> >>         CompanyName, -- name value (child of dict)
>> >>         null,  -- id key
>> >>         null -- id value
>> >>
>> >>         FROM Customers
>> >>
>> >>
>> >>
>> >> UNION ALL
>> >>
>> >>     SELECT     10 as Tag, 7 as Parent,
>> >>

"Don Miller" wrote:

> WOW. In only 26 lines. This code does exactly what I need. Now I'll have to
> spend all day learning about XML PATH and decoding exactly what you did
> (expertly). I'm going to throw away XML EXPLICIT. Thanks.
>
> "Bob" <B**@discussions.microsoft.com> wrote in message
> news:F8017C37-EE8F-4858-A4B6-4EDA6F296E57@microsoft.com...
> > Not sure exactly what your customer table looks like, but here's a demo
> > with
> > SQL 2005 FOR XML PATH:
> >
> > USE tempdb
> > GO
> >
> > CREATE TABLE #customers ( CustomerID INT PRIMARY KEY, CompanyName
> > VARCHAR(50) UNIQUE NOT NULL  )
> > GO
> >
> > INSERT INTO #customers VALUES ( 2776, 'Shore Company' )
> > INSERT INTO #customers VALUES ( 1408, 'Szabo' )
> > INSERT INTO #customers VALUES ( 280, 'Zeba Manufacturing' )
> > INSERT INTO #customers VALUES ( 394, 'Zorba' )
> > GO
> >
> > SELECT
> > 'Letter' AS "dict/key",
> > Letter AS "dict/string",
> > 'Customers' AS "dict/key",
> > (
> > SELECT
> > 'Name' AS "dict/key",
> > CompanyName AS "dict/string",
> > 'ID' AS "dict/key",
> > CustomerID AS "dict/integer"
> > FROM
> > (
> > SELECT CompanyName, CustomerID
> > FROM #customers c
> > WHERE letters.Letter = LEFT( CompanyName, 1 )
> > ) x
> > ORDER BY CompanyName
> > FOR XML PATH(''), ROOT('array'), TYPE
> > ) AS "dict"
> > FROM
> > (
> > SELECT DISTINCT LEFT( CompanyName, 1 ) AS Letter
> > FROM #customers
> > ) letters
> > ORDER BY letters.Letter
> > FOR XML PATH(''), ROOT('array')
> >
> > HTH
> > wBob
> > Rate the post
> >
> > "Don Miller" wrote:
> >
> >> Both SQL 2000 and 2005 (most of my customers migrated to 2005 though).
> >> After
> >> spending all day on this (I'm must be an imbecile) if there is an easier
> >> way
> >> with 2005, I'm open.
> >>
> >> I've come closer to the goal when I used the element directive although
> >> never right.
> >>
> >>     SELECT   1 as Tag, NULL as Parent,
> >>
> >>         null as [array!1], -- parent array
> >>         null as [dict!2!key!element],
> >>         null as [dict!2!string!element],
> >>         null as [dict!2!key!element],
> >>         null as [array!3!dict!element],
> >>         null as [dict!4!key!element],
> >>         null as [dict!4!string!element],
> >>         null as [dict!4!key!element],
> >>         null as [dict!4!integer!element]
> >>
> >> UNION ALL
> >>
> >>     SELECT  Distinct 2 as Tag, 1 as Parent,
> >>
> >>         null, -- parent array
> >>         'Letter', -- child dict
> >>         left(CompanyName, 1), --  dict Letter key
> >>         'Customers', -- letter value
> >>        null,
> >>        null,
> >>        null,
> >>        null,
> >>        null
> >>
> >>  FROM Customers
> >>
> >> UNION ALL
> >>
> >>     SELECT  Distinct 3 as Tag, 2 as Parent,
> >>
> >>         null, -- parent array
> >>         'Letter', -- child dict
> >>         left(CompanyName, 1), --  dict Letter key
> >>         'Customers', -- letter value
> >>        null,
> >>        null,
> >>        null,
> >>        null,
> >>        null
> >>
> >>  FROM Customers
> >>
> >> UNION ALL
> >>
> >>     SELECT  Distinct 4 as Tag, 3 as Parent,
> >>
> >>         null, -- parent array
> >>         'Letter', -- child dict
> >>         left(CompanyName, 1), --  dict Letter key
> >>         'Customers', -- letter value
> >>        null,
> >>        'Name',
> >>       CompanyName,
> >>      ' ID',
> >>      CustomerID
> >>
> >>  FROM Customers
> >>
> >> order by [dict!2!string!element]
> >>
> >> FOR XML EXPLICIT
> >>
> >>
> >>
> >> "Bob" <B**@discussions.microsoft.com> wrote in message
> >> news:FCE502F9-D5EB-4C3F-9F7C-B9B885B341A5@microsoft.com...
> >> > Are you using SQL 2000 or 2005?
> >> >
> >> > "Don Miller" wrote:
> >> >
> >> >> I guess I don't understand XML EXPLICIT or don't know how to go more
> >> >> than
> >> >> a
> >> >> level or two deep. What I want as output is below (along with my query
> >> >> attempt below it). Basically, what I get is something like:
> >> >>
> >> >> <array>
> >> >>    <dict>
> >> >>       <key>Letter</key>
> >> >>       <string>S</string>
> >> >>       <string>T</string>
> >> >>       <string>U</string>
> >> >>      ...
> >> >>
> >> >> So, what am I doing wrong? (and also, how would I order the Letters?)
> >> >>
> >> >> Thanks for any tips.
> >> >>
> >> >>
> >> >> DESIRED OUTPUT
> >> >>
> >> >> <array>
> >> >>    <dict>
> >> >>       <key>Letter</key>
> >> >>       <string>S</string>
> >> >>       <key>Customers</key>
> >> >>       <array>
> >> >>          <dict>
> >> >>             <key>Name</key>
> >> >>             <string>Shore Company</string>
> >> >>             <key>ID</key>
> >> >>             <integer>2776</integer>
> >> >>          </dict>
> >> >>          <dict>
> >> >>             <key>Name</key>
> >> >>             <string>Szabo</string>
> >> >>             <key>ID</key>
> >> >>             <integer>1408</integer>
> >> >>          </dict>
> >> >>       </array>
> >> >>    </dict>
> >> >>    <dict>
> >> >>       <key>Letter</key>
> >> >>       <string>Z</string>
> >> >>       <key>Customers</key>
> >> >>       <array>
> >> >>          <dict>
> >> >>             <key>Name</key>
> >> >>             <string>Zeba Manufacturing</string>
> >> >>             <key>ID</key>
> >> >>             <integer>280</integer>
> >> >>          </dict>
> >> >>          <dict>
> >> >>             <key>Name</key>
> >> >>             <string>Zorba</string>
> >> >>             <key>ID</key>
> >> >>             <integer>394</integer>
> >> >>          </dict>
> >> >>       </array>
> >> >>    </dict>
> >> >>    ...
> >> >> </array>
> >> >>
> >> >> QUERY THAT DOES NOT WORK
> >> >>
> >> >> USE NORTHWIND
> >> >>
> >> >>     SELECT  1 as Tag, NULL as Parent,
> >> >>
> >> >>         null as [array!1], -- parent array
> >> >>         null as [dict!2], -- child dict
> >> >>         null as [key!3], --  dict Letter key
> >> >>         null as [string!4], -- letter value
> >> >>         null as [key!5], -- Customers key
> >> >>         null as [array!6], -- array child of dict
> >> >>         null as [dict!7], -- dict child of array
> >> >>         null as [key!8], -- name key child of dict
> >> >>         null as [string!9], -- name value (child of dict)
> >> >>         null as [key!10],  -- id key
> >> >>         null as [integer!11] -- id value
> >> >>
> >> >> UNION ALL
> >> >>
> >> >>     SELECT     2 as Tag, 1 as Parent,
> >> >>
> >> >>         null, -- parent array
> >> >>         null, -- child dict *****
> >> >>         null, --  dict Letter key
> >> >>         null, -- letter value
> >> >>         null, -- Customers key
> >> >>         null, -- array child of dict
> >> >>         null, -- dict child of array
> >> >>         null, -- name key child of dict
> >> >>         null, -- name value (child of dict)
> >> >>         null,  -- id key
> >> >>         null -- id value
> >> >>
> >> >> UNION ALL
> >> >>
> >> >>     SELECT     3 as Tag, 2 as Parent,
> >> >>
> >> >>         null, -- parent array
> >> >>         null, -- child dict
> >> >>         'Letter', --  dict Letter key
> >> >>         null, -- letter value
> >> >>         null, -- Customers key
> >> >>         null, -- array child of dict
> >> >>         null, -- dict child of array
> >> >>         null, -- name key child of dict
> >> >>         null, -- name value (child of dict)
> >> >>         null,  -- id key
> >> >>         null -- id value
> >> >>
> >> >> UNION ALL
> >> >>
> >> >>     SELECT  DISTINCT   4 as Tag, 2 as Parent,
> >> >>
> >> >>         null, -- parent array
> >> >>         null, -- child dict
> >> >>         null, --  dict Letter key
> >> >>         left(CompanyName, 1), -- letter value
> >> >>         null, -- Customers key
> >> >>         null, -- array child of dict
> >> >>         null, -- dict child of array
> >> >>         null, -- name key child of dict
> >> >>         null, -- name value (child of dict)
> >> >>         null,  -- id key
> >> >>         null
> >> >>
> >> >>         FROM Customers
> >> >>
> >> >>
> >> >>
> >> >> UNION ALL
> >> >>
> >> >>     SELECT     5 as Tag, 2 as Parent,
> >> >>
> >> >>         null, -- parent array
> >> >>         null, -- child dict
> >> >>         null, --  dict Letter key
> >> >>         null, -- letter value
> >> >>         'Customers', -- Customers key
> >> >>         null, -- array child of dict
> >> >>         null, -- dict child of array
> >> >>         null, -- name key child of dict
> >> >>         null, -- name value (child of dict)
> >> >>         null,  -- id key
> >> >>         null -- id value
> >> >>
> >> >> UNION ALL
> >> >>
> >> >>     SELECT     6 as Tag, 2 as Parent,
> >> >>
> >> >>         null, -- parent array
> >> >>         null, -- child dict
> >> >>         null, --  dict Letter key
> >> >>         null, -- letter value
> >> >>         null, -- Customers key
> >> >>         null, -- array child of dict  ****
> >> >>         null, -- dict child of array
> >> >>         null, -- name key child of dict
> >> >>         null, -- name value (child of dict)
> >> >>         null,  -- id key