Ошибка NVarchar(max) to XML

Question

Имеют следующую конфигурацию: Delphi XE7 MS SQL Server 2008 Подключение к серверу SQL с FireDAC

Я использую хранимые процедуры для извлечения таблиц со всеми подробными записями с сервера SQL в формате XML, преобразованном в nvarchar(max). Вот пример:

CREATE PROCEDURE uspUsers_GetAll
  @ReturnData nvarchar(max) = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SET @ReturnData = CONVERT(nvarchar(max),
                (SELECT *, (  SELECT UserID, RightType
                              FROM UserRights
                              WHERE UserRights.UserID = Users.ID
                              FOR XML RAW('UserRight'), 
                              ROOT('UserRights'), ELEMENTS, TYPE)
                FROM Users
                FOR XML RAW('Users'), 
                ROOT('root') , ELEMENTS));
END;

На стороне Delphi я вызываю эту хранимую процедуру, загружаю ее в XML и пытаюсь разобрать, чтобы преобразовать в локальный список объектов. Что-то вроде этого:

procedure TUsers.LoadFromDatabase;
 var
  usersXML: String;
  xmlDoc: IXMLDocument;
  workNode: IXMLNode;
  userObj: TUser;
begin
  Items.Clear;

  with dmApp.uspWork do
  begin
    Close;
    if Prepared then
      Prepared := False;
    Params.Clear;
    StoredProcName := 'uspUsers_GetAll';
    Prepare;
    ExecProc;
  end;

  usersXML := dmApp.uspWork.Params.ParamByName('@ReturnData').Value;
  xmlDoc := TXMLDocument.Create(nil);
  xmlDoc.LoadFromXML(usersXML);
  workNode := xmlDoc.DocumentElement;
  workNode := workNode.ChildNodes.FindNode('Users');

  while (workNode <> nil) and (workNode.NodeName = 'Users') do
  begin
    userObj := TUser.Create;
    userObj.LoadFromXMLNode(workNode);
    Items.Add(userObj);

    workNode := workNode.NextSibling;
  end;
end;

В приведенном выше коде

workNode := workNode.ChildNodes.FindNode('Users');

строка возвращает исключение где-то глубоко в модуле Xml.XMLDoc. Не сообщение об ошибке с конкретной проблемой, которую я мог бы исправить. Попытался сохранить xmlDoc в xml-файл, и он выглядит хорошо (можно открыть его всеми видами инструментов, такими как Firefox).

Странно то, что если я изменяю @ReturnData на varchar (max), я больше не получаю эту ошибку.

Есть идеи, что я делаю не так?

Answer 1

Для XML избегайте использования * и безымянных столбцов. Кроме того, не используйте ROOT в своем вложенном XML, вместо этого объявите его как именованное поле. Например:

CREATE PROCEDURE uspUsers_GetAll
  @ReturnData nvarchar(max) = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SET @ReturnData = CONVERT(nvarchar(max),
                (SELECT givenname = Users.GivenName,surname = Users.Surname   , emailaddress = Users.Email , userrights = (  SELECT userid = UserRights.UserID, righttype = UserRights.RightType
                              FROM UserRights
                              WHERE UserRights.UserID = Users.ID
                              FOR XML RAW('userrights'), 
                              ELEMENTS, TYPE)
                FROM Users
                FOR XML RAW('user'), 
                ROOT('root') , ELEMENTS));
END;

Кстати, я всегда избегаю заглавных XML-тегов, называй меня старомодным. Также обратите внимание, что тег строки 'users' должен быть 'user', поскольку это относится к одному пользователю. Это должно дать вам

<root>
    <user>
       <givenname>Jo</givenname>
       <surname>Shmo</surname>
              <userrights>
                     <userid>12</userid>
                     <righttype>some_type</righttype>
              </userrights>
              <userrights>
                     <userid>12</userid>
                     <righttype>some_type</righttype>
              </userrights>
   </user>
   <user>
    ....
   </user>
</root>