SQL for xml auto elements wrap node

I have the following code:

我有以下代码:

SELECT distinct 
msi.SupplierNumber
    ,Province.[Name] [ProvinceName]
    ,District.[Code] [DistrictCode]
    ,District.[Name] [DistrictName]
    ,Municipality.[Code] [MunicipalityCode]
    ,Municipality.[Name] [MunicipalityName]
    ,City.[Code] [CityCode]
    ,City.[Name] [CityName]
    ,Suburb.[Code] [SuburbCode]
    ,Suburb.[Name] [SuburbName]
    ,Ward.[Code] [WardCode]
FROM
    dbo.MasterSupplierIdentification msi WITH (NOLOCK)
    INNER JOIN dbo.[LinkMasterSupplierCommodity] lmsc WITH (NOLOCK) ON lmsc.MasterSupplierIdentificationID = msi.MasterSupplierIdentificationID
    INNER JOIN dbo.[MasterCommodityGroup] mcg WITH (NOLOCK) ON mcg.[MasterCommodityGroupID] = lmsc.MasterCommodityID
    INNER JOIN dbo.[MasterCommodityLocation] mcl WITH (NOLOCK) ON mcl.[MasterCommodityID] = mcg.[MasterCommodityID]
    INNER JOIN dbo.[MDWard] Ward WITH (NOLOCK) ON Ward.[WardID] = mcl.[WardID]
    INNER JOIN dbo.[MDSuburb] Suburb WITH (NOLOCK) ON Suburb.[SuburbID] = Ward.[SuburbID]
    INNER JOIN dbo.[MDCity] City WITH (NOLOCK) ON City.[CityID] = Suburb.[CityID]
    INNER JOIN dbo.[MDMunicipality] Municipality WITH (NOLOCK) ON Municipality.[MunicipalityID] = City.[MunicipalityID]
    INNER JOIN dbo.[MDDistrict] District WITH (NOLOCK) ON District.[DistrictID] = Municipality.[DistrictID]
    INNER JOIN dbo.[MDProvince] Province WITH (NOLOCK) ON Province.[ProvinceID] = District.[ProvinceID]
WHERE 
    msi.SupplierNumber = 'MAAA0000002'
    and Suburb.Code = '181001001'
for xml auto, Root('Suppliers'), elements xsinil

Which returns:

<Suppliers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <msi>
        <SupplierNumber>MAAA0000002</SupplierNumber>
        <Province>
        <ProvinceName>Western Cape</ProvinceName>
            <District>
            <DistrictCode>105</DistrictCode>
            <DistrictName>Central Karoo</DistrictName>
                <Municipality>
                <MunicipalityCode>181</MunicipalityCode>
                <MunicipalityName>Laingsburg</MunicipalityName>
                    <City>
                    <CityCode>181001</CityCode>
                    <CityName>Laingsburg NU</CityName>
                        <Suburb>
                        <SuburbCode>181001001</SuburbCode>
                        <SuburbName>Laingsburg NU</SuburbName>
                            <Ward>
                                <WardCode>1</WardCode>
                            </Ward>
                            <Ward>
                                <WardCode>12</WardCode>
                            </Ward>
                            <Ward>
                                <WardCode>2</WardCode>
                            </Ward>
                       </Suburb>
                   </City>
               </Municipality>
           </District>
       </Province>
  </msi>

In this specific example, the Wards repeat. I would like to have it wrapped in a parent node . Seems the only way to do this is to use sub query where I specify not AUTO but PATH and ROOT in the sub query and return it as TYPE.

在这个具体的例子中,Wards重复。我想将它包装在父节点中。似乎这样做的唯一方法是使用子查询,其中我指定不是AUTO而是在子查询中指定PATH和ROOT并将其作为TYPE返回。

1 个解决方案

#1

Your final sentence is correct. If you want to see nested data (1:many relations) wrapped in a parent node, you must include them as one block.

你的最后一句是正确的。如果要查看父节点中包含的嵌套数据(1:多关系),则必须将它们包含为一个块。

One hint: You use WITH (NOLOCK) everywhere. Unless you really know what you are doing an unless you have a quite good reason for this, this is a dangerous thing. Very common, but not good practice...

一个提示:你到处都使用WITH(NOLOCK)。除非你真的知道你在做什么,除非你有充分的理由,这是一件危险的事情。很常见,但不是很好的做法......

Try it like this (untested):

试试这样(未经测试):

SELECT distinct 
msi.SupplierNumber
    ,Province.[Name] [ProvinceName]
    ,District.[Code] [DistrictCode]
    ,District.[Name] [DistrictName]
    ,Municipality.[Code] [MunicipalityCode]
    ,Municipality.[Name] [MunicipalityName]
    ,City.[Code] [CityCode]
    ,City.[Name] [CityName]
    ,Suburb.[Code] [SuburbCode]
    ,Suburb.[Name] [SuburbName]

    ,WardXml.XmlAsBlock
FROM
    dbo.MasterSupplierIdentification msi WITH (NOLOCK)
    INNER JOIN dbo.[LinkMasterSupplierCommodity] lmsc WITH (NOLOCK) ON lmsc.MasterSupplierIdentificationID = msi.MasterSupplierIdentificationID
    INNER JOIN dbo.[MasterCommodityGroup] mcg WITH (NOLOCK) ON mcg.[MasterCommodityGroupID] = lmsc.MasterCommodityID
    INNER JOIN dbo.[MasterCommodityLocation] mcl WITH (NOLOCK) ON mcl.[MasterCommodityID] = mcg.[MasterCommodityID]
    INNER JOIN dbo.[MDWard] Ward WITH (NOLOCK) ON Ward.[WardID] = mcl.[WardID]
    INNER JOIN dbo.[MDSuburb] Suburb WITH (NOLOCK) ON Suburb.[SuburbID] = Ward.[SuburbID]
    INNER JOIN dbo.[MDCity] City WITH (NOLOCK) ON City.[CityID] = Suburb.[CityID]
    INNER JOIN dbo.[MDMunicipality] Municipality WITH (NOLOCK) ON Municipality.[MunicipalityID] = City.[MunicipalityID]
    INNER JOIN dbo.[MDDistrict] District WITH (NOLOCK) ON District.[DistrictID] = Municipality.[DistrictID]
    INNER JOIN dbo.[MDProvince] Province WITH (NOLOCK) ON Province.[ProvinceID] = District.[ProvinceID]

    CROSS APPLY
    (
      SELECT WardCode FROM dbo.[MDWard] Ward WITH (NOLOCK) 
      WHERE Ward.[WardID] = mcl.[WardID]
      FOR XML PATH('Ward'),TYPE
    ) AS WardXml(XmlAsBlock)
WHERE 
    msi.SupplierNumber = 'MAAA0000002'
    and Suburb.Code = '181001001'
for xml auto, Root('Suppliers'), elements xsinil

#1