'For Xml Path' in SQL Server

Question

I'm trying get the following output with "FOR XML PATH".

<?xml version="1.0" encoding="utf-8"?>
<List Name=”test name”>
    <Columns>
       <c>Column1</c>
       <c>Column2</c>
       <c>Column3</c>
    </Columns>
    <Rows>  
        <r>
            <v>Data1Column1</v>
            <v>Data1Column2</v>
            <v>Data1Column3</v>
        </r>
        <r>
            <v>Data2Column1</v>
            <v>Data2Column2</v>
            <v>Data2Column3</v>
        </r>
    </Rows>
</List>

I'm trying to tackle the rows section with the following:

SELECT 
    [FA No.] as [v],
    [Location] as [v]
FROM 
    sc.test_rd 
FOR XML PATH (''), ROOT ('Row')

Which returns an output of:

<Row>
  <v>6930151128</v>
  <v>6931151128</v>
  <v>6932151128</v>
  <v>6945151990</v>
  <v>6989151838</v>
</Row>

But I need the following:

<Row>
  <v>6930</v><v>151128</v>
  <v>6931</v><v>151128</v>
  <v>6932</v><v>151128</v>
  <v>6945</v><v>151990</v>
  <v>6989</v><v>151838</v>
</Row>

Any help would be appreciated.

The Nulls worked , but now I have to combine this

SELECT COLUMN_NAME as [c/*] 
FROM INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='test_rd'
FOR XML PATH ('') , ROOT ('Columns');

Which gives me an output of:

<Columns>
  <c>id</c>
  <c>FA No.</c>
  <c>Location</c>
  <c>Location Name</c>
  <c>Line of Business</c>
  <c>LOB Name</c>
  <c>Area/CMU/Paypoint</c>
  <c>Model Code</c>
</Columns>

with this

SELECT 
[FA No.] as [v],
null,
[Location] as [v],
null,
[Line of Business] as [v],
null,
[Area/CMU/Paypoint] as [v],
null,
[Model Code] as [v]
FROM sc.test_rd
FOR XML path ('r') , Root ('Row')

Which gives me an output of this:

<Row>
  <r>
    <v>6930</v>
    <v>151128</v>
    <v>2100</v>
    <v>2</v>
    <v>CMU3+</v>
  </r>
  <r>
    <v>6931</v>
    <v>151128</v>
    <v>2100</v>
    <v>3</v>
    <v>CMU3+</v>
  </r>
  <r>
    <v>6932</v>
    <v>151128</v>
    <v>2100</v>
    <v>1</v>
    <v>CMU3+</v>
  </r>
  <r>
    <v>6945</v>
    <v>151990</v>
    <v>2100</v>
    <v>2</v>
    <v>CMU3+</v>
  </r>
  <r>
    <v>6989</v>
    <v>151838</v>
    <v>2100</v>
    <v>2</v>
    <v>CMU3+</v>
  </r>
</Row>

Another sample of the desired output:

<?xml version="1.0" encoding="utf-8"?>
<List Name='test_rd' Action='Update'>
 <Columns><c>FA No.</c><c>Location</c><c>Location Name</c><c>Line of Business</c><c>LOB Name</c><c>Area/CMU/Paypoint</c><c>Model Code</c></Columns>
 <Rows>
<r><v>6930</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>1</v><v>CMU3+</v></r>
<r><v>6931</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>2</v><v>CMU3+</v></r>
<r><v>6932</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>3</v><v>CMU3+</v></r>
<r><v>6933</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>4</v><v>CMU3+</v></r>
<r><v>6934</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>5</v><v>CMU3+</v></r>

 </Rows>
</List>

I also would like to add a CASE option, but I keep on getting an error?

-- test_rd
    SELECT 'test_rd' as [@Name], 'Update' as [@Action]
, (
    SELECT COLUMN_NAME as [c/*] 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME='test_rd' AND TABLE_SCHEMA = 'sc' and  COLUMN_NAME != 'id'
    FOR XML PATH (''), TYPE, ROOT ('Columns')
    )
, (
    SELECT 
        [FA No.] as [v],
        NULL,
        [Location] as [v],
        NULL,
        [Location Name] as [v],
        NULL,
        [Line of Business]as [v],
        NULL,
        [Line of Business Name]as [v],
          CASE WHEN [Line of Business Name] = '0' THEN ''
            WHEN [Line of Business Name] = '1' THEN 'lob1'
            WHEN [Line of Business Name] = '2' THEN 'lob2'
          End as [Line of Business Name],
        NULL,
        [Area/CMU/Paypoint] as [v],
        NULL,
        [Model Code] as [v]
    FROM sc.test_rd
    FOR XML path ('r'), TYPE, ROOT ('Rows')
)
FOR XML PATH('List') 
-- test_rd

Lookup Error - SQL Server Database Error: Column name 'Line of Business Name' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

Answer 1

Here is a minimal reproducible example. I used AdventureWorks2012 database and its narrow Currency table with three columns.

SQL

USE AdventureWorks2012;
GO

SELECT 'test_rd' AS [@Name], 'Update' AS [@Action]
, (
    SELECT COLUMN_NAME as [c/*] 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME='Currency' AND TABLE_SCHEMA = 'Sales' AND COLUMN_NAME != 'CurrencyCode'
    FOR XML PATH (''), TYPE, ROOT ('Columns')
    )
, (
    SELECT TOP(2)
        [CurrencyCode] as [v],
        NULL,
        [Name] as [v],
        NULL,
        [ModifiedDate] as [v]
    FROM Sales.Currency
    FOR XML path ('r'), TYPE, ROOT ('Row')
)
FOR XML PATH('List'), TYPE;

Output

<List Name="test_rd" Action="Update">
  <Columns>
    <c>Name</c>
    <c>ModifiedDate</c>
  </Columns>
  <Row>
    <r>
      <v>AED</v>
      <v>Emirati Dirham</v>
      <v>2002-06-01T00:00:00</v>
    </r>
    <r>
      <v>AFA</v>
      <v>Afghani</v>
      <v>2002-06-01T00:00:00</v>
    </r>
  </Row>
</List>

Answer 2

Take a peek at the following... notice the NULL in the select

Example

Declare @YourTable Table ([FA No.] varchar(50),[Location] varchar(50))
Insert Into @YourTable Values 
 (6930,151128)
,(6931,151128)
,(6932,151128)
,(6945,151990)
,(6989,151838)
 

SELECT 
[FA No.] as [v],
null,
[Location] as [v]
FROM @YourTable
FOR XML path ('') , Root ('Row')

Returns

<Row>
  <v>6930</v>
  <v>151128</v>
  <v>6931</v>
  <v>151128</v>
  <v>6932</v>
  <v>151128</v>
  <v>6945</v>
  <v>151990</v>
  <v>6989</v>
  <v>151838</v>
</Row>

EDIT -

A little unclear what you are looking for, but you can have a sub-query with the TYPE in you XML creation. Look at Rows = and notice the ,TYPE

Example

Select [List/@Name] = YourNameColumn
      ,Rows = ( 
                Select  [FA No.] as [v],
                        null,
                        [Location] as [v]
                 From   sc.test_rd 
                FOR XML path ('') , Root ('Row'),TYPE
              )
  From SomeTable