[MSSQL2000] for xml explicit

[BulMi]

Hallo,

Ik heb een vraag over de FOR XML EXPLICIT optie in MSSQL 2000.

Ik heb 4 tabellen, namelijk:

Language
- LanguageId
- Name

LanguageCategory
- LanguageCategoryId
- Name

LanguageRanking
- LanguageRankingId
- Ranking

EmployeeLanguage
- EmployeeLanguageId
- EmployeeId
- LanguageId
- LanguageCategoryId
- LanguageRankingId


Nu zou ik graag de volgende xml structuur uit de tabellen willen halen:

<Languages>
<Language>
<LanguageName>Nederlands</LanguageName>
<Reading>Moedertaal</Reading>
<Writing>Moedertaal</Writing>
</Language>
<Language>
<LanguageName>Engels</LanguageName>
<Reading>Vloeiend</Reading>
<Writing>Vloeiend</Writing>
</Language>
<Language>
<LanguageName>Duits</LanguageName>
<Reading>Vloeiend</Reading>
<Writing>Redelijk</Writing>
</Language>
</Languages>

Echter krijg ik:

<Languages>
<Language>
<LanguageName>Nederlands</LanguageName>
</Language>
<Language>
<LanguageName>Engels</LanguageName>
</Language>
<Language>
<LanguageName>Duits</LanguageName>
</Language>
<Language>
<Reading>Moedertaal</Reading>
</Language>
<Language>
<Reading>Vloeiend</Reading>
</Language>
<Language>
<Reading>Vloeiend</Reading>
</Language>
<Language>
<Writing>Moedertaal</Writing>
</Language>
<Language>
<Writing>Vloeiend</Writing>
</Language>
<Language>
<Writing>Redelijk</Writing>
</Language>
</Languages>

Ik maak gebruik van de volgende query:

SELECT
1 AS TAG,
NULL AS Parent,
NULL AS [Languages!1!Language!Element],
NULL AS [Language!2!LanguageName!Element],
NULL AS [Language!3!Reading!Element],
NULL AS [Language!4!Writing!Element],
NULL AS [Language!2!LanguageId!Hide]
FROM
Employee
WHERE employeeId = 11

UNION ALL

SELECT distinct
2 AS TAG,
1 AS Parent,
NULL AS [Languages!1!Language!Element],
L.Name AS [Language!2!LanguageName!Element],
NULL AS [Language!3!Reading!Element],
NULL AS [Language!4!Writing!Element],
EL.LanguageId AS [Language!2!LanguageId!Hide]

FROM EmployeeLanguage EL
INNER JOIN language L
ON EL.LanguageId = L.LanguageId
WHERE EL.employeeId = 11


UNION ALL

SELECT
3 AS TAG,
1 AS Parent,
NULL AS [Languages!1!Language!Element],
NULL AS [Language!2!LanguageName!Element],
LR.Ranking AS [Language!3!Reading!Element],
NULL AS [Language!4!Writing!Element],
EL.LanguageId AS [Language!2!LanguageId!Hide]

FROM EmployeeLanguage EL
INNER JOIN LanguageCategory LC
ON EL.LanguageCategoryId = LC.LanguageCategoryId
INNER JOIN LanguageRanking LR
ON EL.LanguageRankingId = LR.LanguageRankingId
WHERE EL.employeeId = 11 AND
LC.Name = 'Lezen'

UNION ALL

SELECT
4 AS TAG,
1 AS Parent,
NULL AS [Languages!1!Language!Element],
NULL AS [Language!2!LanguageName!Element],
NULL AS [Language!3!Reading!Element],
LR.Ranking AS [Language!4!Writing!Element],
EL.LanguageId AS [Language!2!LanguageId!Hide]

FROM EmployeeLanguage EL
INNER JOIN LanguageCategory LC
ON EL.LanguageCategoryId = LC.LanguageCategoryId
INNER JOIN LanguageRanking LR
ON EL.LanguageRankingId = LR.LanguageRankingId
WHERE EL.employeeId = 11 AND
LC.Name = 'Schrijven'

ORDER BY [Language!2!LanguageId!Hide]

FOR XML EXPLICIT

Wie kan mij helpen?

[ Voor 27% gewijzigd door BulMi op 18-04-2006 11:15 ]

[whoami]

Waarom gebruik je een union , en join je de tabellen niet ?
Op de manier waarop je het nu doet, kan sql imo onmogelijk weten wat er bij wat hoort, en kan hij dus ook die Xml niet maken zoals jij het wilt.

[BulMi]

ik heb voorbeelden gebruikt van de msdn en andere sites:

http://msdn.microsoft.com...xmlExplicitListing_02.txt

http://msdn.microsoft.com...ag01/html/xmlExplicit.asp

http://www.informit.com/a.../article.asp?p=24976&rl=1


Het voorbeeld dat ik geef is ook maar 1 deel van de database structuur. Dit is het languages deel van de employees, maar er zijn nog 4-5 van zulke blokken waarbij employee centraal staat. Ik hoop dan ook dat ik het in die 4-5 blokken kan opdelen en niet 1 grote query moet schrijven waar ik alle 20 tabellen aan elkaar moet joinen.

[joopst]

NULL AS [Language!3!Reading!Element],
NULL AS [Language!4!Writing!Element],

die zitten niet op hetzelfde nivo (wat wel zou moeten dan)

[BulMi]

ik heb ook geprobeerd beide(reading en writing) op niveau 3 en alle 3(reading, writing en name) op niveau 2 te zetten, maar het resultaat blijft hetzelfde

[ Voor 29% gewijzigd door BulMi op 19-04-2006 09:39 ]

[joopst]

Ik heb wel een voorbeeldje voor je van hoe het kan zonder die unions. Dan kan je aan de hand van een voorbeeld lekker puzzelen hoe het werkt

code:

ALTER Procedure sp_setting_getxml
/******************************************************************************
    Desc: retrieves the settings in xml format

    <setting-block-list>
        <setting-block xml-tag="authentication">
            <setting xml-tag="session-timout-minutes">240</setting>
        </setting-block>
        <setting-block xml-tag="email">
            <setting xml-tag="layout-template">layout-template</setting>
            <setting xml-tag="content-template-standard">content-template-standard</setting>
            <setting xml-tag="content-template-todo">content-template-todo</setting>
            <setting xml-tag="content-template-logging">content-template-logging</setting>
            <setting xml-tag="tag-van-een-big-value-setting" />
        </setting-block>
    </setting-block-list>

*/

AS
    DECLARE @SpName NVARCHAR(50) -- for error message formatting
    -- no need for counting
    SET NOCOUNT ON

    SELECT 
        -- calculate xml hierarchy
        1+ ISNULL(Level0.Id,0) + ISNULL(Level1.Id,0) as tag 
        , ISNULL(Level0.Id,0) + ISNULL(Level1.Id,0) as Parent

        -- define xml elements and attributes
        , null as [setting-block-list!1]
        , SettingsBlock.XMLTagName as [setting-block!2!xml-tag]
        
        , Setting.XMLTagName    as [setting!3!xml-tag]
        , Setting.Value     as [setting!3!!element]

    -- from root level and base table
    FROM (SELECT 0 as [Id] UNION SELECT 1) Level0   -- setup level 1
    LEFT OUTER JOIN SettingsBlock ON Level0.[Id] = 1   -- data for level 1

    -- join second level table
    LEFT OUTER JOIN (SELECT 0 as [Id] UNION SELECT 1) Level1 ON Level0.[Id] = 1   -- setup level 2
    LEFT OUTER JOIN Setting ON Level1.[Id] = 1 AND Setting.SettingsBlockId = SettingsBlock.SettingsBlockId -- data for level 2

    -- prevent empty element appearing when no results
    WHERE NOT(Level0.Id = 1 AND SettingsBlock.SettingsBlockId IS NULL)
    
    -- required for xml, sort on keys
    ORDER BY SettingsBlock.SettingsBlockId, SETTINGS.SettingId
    
    FOR XML EXPLICIT
GO

happy puzzling )