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

Why is the XML element <v> for both [FA No.] and [Location]? It makes the XML out impossible to parse. It seems like one of these would be easier to parse: <v id1="6930" id2="151128" /> or <v><id1>6930</id1><id2>151128</id2></v> — James L.
– James L., Commented Sep 17, 2020 at 18:34

John Cappelletti · Accepted Answer · 2020-09-17 19:15:56Z

1

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

edited Sep 17, 2020 at 19:15

answered Sep 17, 2020 at 18:34

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Cappelletti Over a year ago

@Bob Take a peek at the EDIT. If you provide sample data, I'm sure we could get to your desired results.

Bob Over a year ago

Thanks John, I added the desired output in the intial post.

Yitzhak Khabinsky · Accepted Answer · 2020-09-18 02:05:00Z

0

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>

edited Sep 18, 2020 at 2:05

answered Sep 17, 2020 at 20:03

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

5 Comments

Bob Over a year ago

Perfect, but how do I omit the first coulmn name, which is <c>CurrencyCode<c/> for you but <c>id</c> for me?

Bob Over a year ago

How do I change the result of this: <List Name="test_rd" Action="Update"> to <List Name='test_rd' Action='Update'>

Yitzhak Khabinsky Over a year ago

@Bob, in XML there is no difference between double or single quote for attribute values. Additionally, SQL Server is using single quote as a delimiter for strings. That's why inside of it double quotes are used.

Bob Over a year ago

okay,got that. I also updated the intial post with another questions related to CASE. Thanks for all the help.

Yitzhak Khabinsky Over a year ago

@Bob, it seems that you have a space somewhere.

Collectives™ on Stack Overflow

'For Xml Path' in SQL Server

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related