Why am I getting error in specifying attributes in XML in SQL Server-CodePudding

I am generating XML in SQL:

I have written a code for it:

SELECT DISTINCT aa_sender_logicalid_schemaversionid   AS
                                        [Sender/LogicalID/@schemeID/@schemeName]

This is a part of a code and I am getting following error on running it:

Column name 'Sender/LogicalID/@schemeID/@schemeName' contains an invalid XML identifier as required by FOR XML; '@'(0x0040) is the first character at fault.

I want to get following attributes in LogicalID tag.

<LogicalID schemeID="schemeID1" schemeName="schemeName1" schemeAgencyID="schemeAgencyID1" schemeAgencyName="schemeAgencyName1" schemeVersionID="schemeVersionID1" schemeDataURI="http://uri1" schemeURI="http://uri1" accountingEntity="accountingEntity1" location="location1" variationID="1" lid="http://uri1">LogicalID1</LogicalID>

Can anyone help to figure out. Thanks.

CodePudding user response：

You cannot specify multiple attributes in a single column alias like Sender/LogicalID/@schemeID/@schemeName, they need to be separate columns each with their aliases such as the following...

select
  ID as [@schemeID],
  Name as [@schemeName],
  AgencyID as [@schemeAgencyID],
  AgencyName as [@schemeAgencyName],
  VersionID as [@schemeVersionID],
  DataURI as [@schemeDataURI],
  URI as [@schemeURI],
  Entity as [@accountingEntity],
  Location as [@location],
  VariationID as [@variationID],
  LID as [@lid],
  LogicalID as [text()]
from --Select from your real table(s) here
(values 
  ('schemeID1', 'schemeName1', 'schemeAgencyID1', 'schemeAgencyName1', 'schemeVersionID1', 'http://uri1', 'http://uri1', 'accountingEntity1', 'location1', '1', 'http://uri1', 'LogicalID1')
) SomeAlias (ID, Name, AgencyID, AgencyName, VersionID, DataURI, URI, Entity, Location, VariationID, LID, LogicalID)
for xml path('LogicalID'), root('Sender');

Which yields XML output similar to the following...

<Sender>
  <LogicalID
    schemeID="schemeID1"
    schemeName="schemeName1"
    schemeAgencyID="schemeAgencyID1"
    schemeAgencyName="schemeAgencyName1"
    schemeVersionID="schemeVersionID1"
    schemeDataURI="http://uri1"
    schemeURI="http://uri1"
    accountingEntity="accountingEntity1"
    location="location1"
    variationID="1"
    lid="http://uri1">LogicalID1</LogicalID>
</Sender>