Блог

Я пытаюсь получить следующий вывод с помощью «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>
  

Я пытаюсь разобраться с разделом строк следующим образом:

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

Который возвращает результат:

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

Но мне нужно следующее:

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

Любая помощь была бы признательна.

Нули сработали , но теперь я должен объединить это

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

Что дает мне результат:

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

с этим

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

Что дает мне вывод этого:

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

Еще один пример желаемого вывода:

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

Я также хотел бы добавить параметр CASE, но я продолжаю получать ошибку?

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

Ошибка поиска — ошибка базы данных SQL Server: имя столбца ‘Название бизнес-линии’ содержит недопустимый XML-идентификатор, как требуется для XML; ‘ ‘(0x0020) является первым символом ошибки.

Взгляните на следующее… обратите внимание на NULL в выборе

Пример

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

ВОЗВРАТ

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

Редактировать —

Немного неясно, что вы ищете, но у вас может быть вложенный запрос с ТИПОМ, который вы создаете в XML. Посмотрите на Rows = и обратите внимание на ,TYPE

Пример

 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
  

Вот минимально воспроизводимый пример. Я использовал базу данных AdventureWorks2012 и ее узкую таблицу валют с тремя столбцами.

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;
  

Вывод

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