CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlfor-xml
okidog
okidog

Reputation: 21

SQL Server query for name value pairs

I'm new at creating an XML file using SQL server. I have 2 tables; 1 for people and the other with default settings for them.

Person table:

ABC   
DEF

Settings table:

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

How can I create an name value pair that looks like this? When I try to get the settings, I received an error about multiple records: 

<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>

Upvotes: 1

Views: 875

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

As told in my comment your given XML is not valid. The following query comes close to the output you requested. Hoping, that my magic glass bulb is still working:

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')

The result

<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>

Upvotes: 2

Related Questions