CODEWITHSUNDEEP
xmlsql-server-2008t-sqlconcatenation
Viper
Viper

Reputation: 2236

Concatenate multiple xml rows into one

Problem:

I want to get changes from an sql server to be printed into a (probably big) xml file. The Sql-Server has ChangeTracking enabled to identify the entities with the changes. Therefore i have written three Scalar-valued Functions which can generate the appropriate XML for a single entity.

  • GenerateAccountXml( int AccountId )
  • GenerateDeviceXml( int DeviceId )
  • GenerateServiceXml( int ServiceId )

    • Also i have three Table-valued Function which returns the Entity-Ids for a specific ChangeTracking Number.

  • GetAccountChangeSets( bigint changeTrackingId )
  • GetDeviceChangeSets( bigint changeTrackingId )
  • GetServiceChangeSets( bigint changeTrackingId )

    • Finally i make a call like this:

    select

(
    select
    (
        select [dbo].GenerateServiceXml( CT.ServiceId )
        from [dbo].GetServiceChangeSets( 22000 ) CT
    ) FOR XML PATH( 'Services' ), TYPE
),

(
    select
    (
        select [dbo].GenerateDeviceXml( CT.DeviceId )
        from [dbo].GetDeviceChangeSets( 22000 ) CT
    ) FOR XML PATH( 'Devices' ), TYPE
),

(
    select
    (
        select [dbo].GenerateAccountXml( CT.AccountId ) )
        from [dbo].GetAccountChangeSets( 22000 ) CT
    ) FOR XML PATH( 'Accounts' ), TYPE
)

FOR XML PATH( 'Changes' ), TYPE

    This works as long as the GetXXXChangeSets Functions return only one EntityId for each Entity, but at the moment i get multiple XML-Rows for each entity. Now the Problem is the following error-message:

    Subquery returned more than 1 value. This is not permitted when the subquery 
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I have tried the STUFF command, but it only accepts a varchar. When i convert the output in a varchar(max) the < and > sign are replaced.

    Are there other solutions for this problem?

    Upvotes: 0

    Views: 2696

    Answers (1)

    JNK
    JNK

    Reputation: 65147

    I think the engine should handle this and give you multiple XML nodes if you change the query slightly. If you want to have a root node for the element collections you may need to change the XML PATH('Service') to say 'XML PATH (''), ROOT('Services')` which will combine all the rows into a single node.

    Essentially I think the issue is you have one too many nested subselects. The built in FOR XML can handle multiple rows in a nested SELECT expression just fine.

    select

(   select [dbo].GenerateServiceXml( CT.ServiceId )
    from [dbo].GetServiceChangeSets( 22000 ) CT
    FOR XML PATH( 'Services' ), TYPE
),

(
    select [dbo].GenerateDeviceXml( CT.DeviceId )
    from [dbo].GetDeviceChangeSets( 22000 ) CT
    FOR XML PATH( 'Devices' ), TYPE
),

(
    select [dbo].GenerateAccountXml( CT.AccountId ) )
    from [dbo].GetAccountChangeSets( 22000 ) CT
    FOR XML PATH( 'Accounts' ), TYPE
)

FOR XML PATH( 'Changes' ), TYPE

    Upvotes: 1

    Related Questions