CODEWITHSUNDEEP
sql-servert-sql
Evan Camilleri
Evan Camilleri

Reputation: 94

Formatting XML in TSQL FOR XML

The following TSQL code is designed to emit data in XML Format. The following code:

SELECT l.wlst_Name [Name]
     , pid.wpid_Number AS [Value]
     , pid.wpid_IsPrimary AS [IsPrimary]
FROM dbo.tb_W_PhoneInternetData AS pid
        INNER JOIN dbo.tb_W_Lists AS l ON pid.wpid_wlst_Type_fk = l.wlst_pk
ORDER BY l.wlst_Name, pid.wpid_IsPrimary DESC
FOR XML RAW('Data'),TYPE

returns the following data, which is OK.... however

<Data Name="Email" Value="[email protected]" IsPrimary="1" />
<Data Name="Fax" Value="21950121" IsPrimary="1" />
<Data Name="ID Number" Value="123435456m" IsPrimary="0" />
<Data Name="Mobile" Value="99977500" IsPrimary="1" />
<Data Name="Phone" Value="23977500" IsPrimary="1" />
<Data Name="Tax No" Value="737 0443 41" IsPrimary="0" />
<Data Name="Website" Value="asdf" IsPrimary="0" />

....but I need it the following format:

<Data Name="Email" IsPrimary="1">[email protected]</Data>
<Data Name="Fax" IsPrimary="1" >21950121</Data>
<Data Name="ID Number" IsPrimary="0" />123435456m</Data>
<Data Name="Mobile" IsPrimary="1" >99977500</Data>
<Data Name="Phone" IsPrimary="1" >23977500</Data>
<Data Name="Tax No" IsPrimary="0" >737 0443 41</Data>
<Data Name="Website" IsPrimary="0" >asdf</Data>

? What can I do ?

Upvotes: 1

Views: 94

Answers (2)

marc_s
marc_s

Reputation: 754258

You should be able to do this with FOR XML PATH - something like this:

SELECT 
    l.wlst_Name AS '@Name',
    pid.wpid_IsPrimary AS '@IsPrimary',
    pid.wpid_Number AS 'text()'
FROM   
    dbo.tb_W_PhoneInternetData AS pid
INNER JOIN 
    dbo.tb_W_Lists AS l ON pid.wpid_wlst_Type_fk = l.wlst_pk
ORDER BY 
    l.wlst_Name, pid.wpid_IsPrimary DESC
FOR XML PATH('Data'), ROOT('AllData')

Basically, what you want to use as attributes needs to be selected with a column alias that starts with a @ sign.

Update: I updated my response - what I had been looking for (and not able to find right away) is the ... AS 'text()' which basically produces the same output (value of the column is output as literal text - without any XML element open/end tags around it) as the "wildcard" character that @MikaelEriksson mentioned.

Upvotes: 2

BartekR
BartekR

Reputation: 3947

As @marc_s wrote - use FOR XML PATH. To get exactly result you may try the following:

CREATE TABLE #test(
    Name        VARCHAR(10),
    Value       VARCHAR(100),
    IsPrimary   BIT
)

INSERT INTO #test (Name, Value, IsPrimary) VALUES
('Email', '[email protected]', 1),
('Fax', '21950121', 1),
('ID Number', '123435456m', 0),
('Mobile', '99977500', 1),
('Phone', '23977500', 1),
('Tax No', '737 0443 41', 0),
('Website', 'asdf', 0)


SELECT
    'Data/@Name'        = Name,
    'Data/@IsPrimary'   = IsPrimary,
    'Data'              = Value
FROM #test
FOR XML PATH(''), ROOT('All'), TYPE

DROP TABLE #test

-- Result
<All>
  <Data Name="Email" IsPrimary="1">[email protected]</Data>
  <Data Name="Fax" IsPrimary="1">21950121</Data>
  <Data Name="ID Number" IsPrimary="0">123435456m</Data>
  <Data Name="Mobile" IsPrimary="1">99977500</Data>
  <Data Name="Phone" IsPrimary="1">23977500</Data>
  <Data Name="Tax No" IsPrimary="0">737 0443 41</Data>
  <Data Name="Website" IsPrimary="0">asdf</Data>
</All>

UPDATE: @Mikael Eriksson suggested star wildcard. Another version below:

SELECT
    '@Name'        = Name,
    '@IsPrimary'   = IsPrimary,
    '*'             = Value
FROM #test
FOR XML PATH('Data'), ROOT('All'), TYPE

Upvotes: 2

Related Questions