Запрос SQL Server для пар имя-значение

Я новичок в создании XML-файла с использованием SQL-сервера. У меня есть 2 таблицы; 1 для людей, а другой с настройками по умолчанию для них.

Таблица Person:

ABC   
DEF

Таблица Настройки:

Code           name                 value           
-----------------------------------------
Hair           Hair color           Brown                  
Hair           Texture              Curly             
Shoes          Shoe size            6            
Shoes          Shoe color           White

Как я могу создать пару «имя-значение», которая выглядит так? При попытке получить настройки я получил ошибку о нескольких записях:

<PersonData>
  <Person>ABC</Person>
    <DefaultSettings>
      <Code>Hair</Code>
      <settings>
        <name>Hair color</name>
        <value>Brown</value>
      </settings>
      <settings>
        <name>Texture</name>
        <value>Curly</value>
      </settings>
      <Code>Shoes</Code>
      <settings>
        <name>Shoe size</name>
        <value>6</value>
      </settings>
      <settings>
        <name>Shoe color</name>
        <value>White</value>
      </settings>
    </DefaultSettings>
  </Person>
  <Person>DEF</Person>
    <DefaultSettings>
      <Code>Hair</Code>
      <settings>
        <name>Hair color</name>
        <value>Brown</value>
      </settings>
      <settings>
        <name>Texture</name>
        <value>Curly</value>
      </settings>
      <Code>Shoes</Code>
      <settings>
        <name>Shoe size</name>
        <value>6</value>
      </settings>
      <settings>
        <name>Shoe color</name>
        <value>White</value>
      </settings>
    </DefaultSettings>
  </Person>
</PersonData>

xml sql sql-server tsql for-xml
person okidog    schedule 26.10.2016    source источник
comment
Вы гуглили «Для XML Sql Server»? Покажите код, который вы пробовали, и объясните, почему он не работает.   -  person dfundako    schedule 26.10.2016
comment
Это не имеет никакого смысла. У всех одинаковые настройки? Код настройки представляет несколько значений?   -  person David דודו Markovitz    schedule 26.10.2016
comment
Ваш XML недействителен. Вы закрываете тег <Person> за именем человека, а затем снова после закрывающего /<DefaultSettings>. Пожалуйста, исправьте ожидаемый результат...   -  person Shnugo    schedule 26.10.2016

Ответы (1)


arrow_upward
2
arrow_downward

Как сказано в моем комментарии, ваш данный XML недействителен. Следующий запрос близок к запрошенному вами результату. Надеясь, что моя волшебная стеклянная лампочка все еще работает:

DECLARE @personTable TABLE(ID INT IDENTITY, Name VARCHAR(100));
INSERT INTO @personTable VALUES
('ABC'),('DEF');

DECLARE @defaultSettings TABLE(Code VARCHAR(100),name VARCHAR(100),value VARCHAR(100));           
INSERT INTO @defaultSettings VALUES 
 ('Hair','Hair color','Brown')                  
,('Hair','Texture','Curly')        
,('Shoes','Shoe size','6')            
,('Shoes','Shoe color','White');

SELECT pd.ID AS [Person/@id]
      ,pd.Name AS [Person/@name]
      ,(
        SELECT ds.Code
              ,(
                SELECT ds2.name
                      ,ds2.value
                FROM @defaultSettings AS ds2
                WHERE ds.Code=ds2.Code
                FOR XML PATH('settings'),TYPE
               )
        FROM @defaultSettings AS ds
        GROUP BY ds.Code
        FOR XML PATH(''),TYPE
       ) AS [Person/DefaultSettings]
FROM @personTable AS pd
FOR XML PATH(''),ROOT ('PersonData')

Результат

<PersonData>
  <Person id="1" name="ABC">
    <DefaultSettings>
      <Code>Hair</Code>
      <settings>
        <name>Hair color</name>
        <value>Brown</value>
      </settings>
      <settings>
        <name>Texture</name>
        <value>Curly</value>
      </settings>
      <Code>Shoes</Code>
      <settings>
        <name>Shoe size</name>
        <value>6</value>
      </settings>
      <settings>
        <name>Shoe color</name>
        <value>White</value>
      </settings>
    </DefaultSettings>
  </Person>
  <Person id="2" name="DEF">
    <DefaultSettings>
      <Code>Hair</Code>
      <settings>
        <name>Hair color</name>
        <value>Brown</value>
      </settings>
      <settings>
        <name>Texture</name>
        <value>Curly</value>
      </settings>
      <Code>Shoes</Code>
      <settings>
        <name>Shoe size</name>
        <value>6</value>
      </settings>
      <settings>
        <name>Shoe color</name>
        <value>White</value>
      </settings>
    </DefaultSettings>
  </Person>
</PersonData>
person Shnugo    schedule 26.10.2016
comment
Спасибо, Шнуго! я попробую это. Я знаю, что данные не имеют смысла, но это макет, чтобы быть дискретным. Мне нужно преобразовать данные в спецификации поставщика. Это часть общих данных PersonData. Можно ли включить запрос в другой запрос? - person okidog; 26.10.2016
comment
@okidog Запрос полностью ad-hoc, поэтому его легко включить в другой запрос. Подумайте о том, чтобы все выражение возвращало одно скалярное значение. Просто поместите его в скобки и добавьте в список столбцов выбора. - person Shnugo; 27.10.2016
comment
Это прекрасно работает! Мне просто нужно выяснить, как избавиться от пространств имен. Большое спасибо! - person okidog; 27.10.2016
comment
Извиняюсь! Ценю ваше руководство по этому поводу. - person okidog; 21.12.2016