tsql: convertir des colonnes dans une colonne xml, xml par ligne

Exemple de ce que j'ai:

table: (col1 int, col2 int)

les données de l'échantillon (les données réelles ne seront pas connus au moment de l'exécution):

1,1
2,2
3,3
4,4

Résultat attendu: une seule colonne (xml)

et 4 lignes

row1: <cols><col1>1</col1><col2>1</col2></cols>
row2: <cols><col1>2</col1><col2>2</col2></cols>
row3: <cols><col1>3</col1><col2>3</col2></cols>
row4: <cols><col1>4</col1><col2>4</col2></cols>

Désolé les gars le modérateur ne voulez pas que le "bruit de réponses" à être supprimé. J'espère qu'il pourra comprendre, je suis d'aider ces pauvres gars si personne ne peut les voir ils ont complètement manqué le point. J'ai été la protection de leur réputation ...

Ici est une façon de réaliser ce que je veux. Mais ce n'est pas une bonne façon parce que le xml est construit à la main et n'est pas correctement encodés.

declare @colsList nvarchar(max)
set @colsList = ''
select @colsList = @colsList + '+ ''<' + COLUMN_NAME + '>'' + Isnull(cast( [' + COLUMN_NAME + '] as nvarchar(max)),'''') + ''</' + COLUMN_NAME + '>'' ' 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'SampleTable';

select @colsList = stuff(@colsList,1,1,'');
exec('select colsValues=cast(' + @colsList + ' as xml) from SampleTable');
  • Softion, veuillez arrêter d'essayer de supprimer les réponses, parce qu'ils "totalement à côté de la question". Au lieu de cela, de clarifier ce que vous êtes en train de demander, soit dans votre question ou un commentaire à leur réponse.
  • J'ai clarifié la question avant de le supprimer. Je supprime donc aucun autre utilisateur ne pourrait être perturbé par des réponses qui ne sont pas pour cette question particulière.
  • -1 pour mettre -1 pour une mauvaise raison. Où pensez-vous que vous êtes. À l'école ? C'est ma question et je le gère comme je veux.
InformationsquelleAutor Softlion | 2011-02-11
  1. 18

    Bien simple, mieux c'est le plus rapide.

    select d=(select a.* for xml path('r'),type,elements absent)
from MyTable a
    InformationsquelleAutor Softlion
  2. 10

    La façon la plus simple est d'utiliser cette:

    SELECT (SELECT T.* FOR XML RAW)
FROM MyTable T

    Ou si vous voulez plus de contrôle sur la structure XML à utiliser:

    SELECT (SELECT T.* FROM (SELECT T.*) T FOR XML AUTO)
FROM MyTable T
    InformationsquelleAutor Char
  3. 1

    MODIFIER

    Pour produire une colonne XML pour chaque ligne, vous pouvez utiliser la sous

    -- this is a poor cousin of a proper sanitize function. Add to it whatever you need
-- to cater for your column names that will be invalid XML tag names
-- example below only removes spaces (xml node names cannot contain spaces)
create function dbo.sanitize(@colname sysname) returns sysname
as begin
return replace(@colname,' ', '')
end
GO

-- test table
create table [test table](i int, s varchar(max), [d t] datetime)
insert [test table] select 1, 'abc', getdate()
insert [test table] select 2, 'def', getutcdate()
insert [test table] select 3, 'g', getdate()+10
insert [test table] select 4, 'hij', getutcdate()+20
GO

-- the dynamic SQL to return each row as an single XML column
declare @tablename sysname set @tablename = 'test table';
declare @colsList nvarchar(max)
select @colsList = isnull(@colsList+',','') +
    QuoteName(column_name) + ' as ' +
    dbo.sanitize(column_name)
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = @tablename;

select @colsList = '
    select (select ' + @colslist + ' for xml path (''cols''))
    from ' + quotename(@tablename)
--print @colslist
exec(@colslist);

    Comme un seul document XML pour l'ensemble de la table

    Simplement utiliser POUR XML, et de spécifier un chemin d'accès de "cols"

    declare @tbl table (col1 int, col2 int)
insert @tbl values (1,1)
insert @tbl values (2,2)
insert @tbl values (3,3)
insert @tbl values (4,4)

-- you can use just the part below here
select col1, col2
from @tbl
for xml path('cols')

    Sortie

    <cols>
  <col1>1</col1>
  <col2>1</col2>
</cols>
<cols>
  <col1>2</col1>
  <col2>2</col2>
</cols>
<cols>
  <col1>3</col1>
  <col2>3</col2>
</cols>
<cols>
  <col1>4</col1>
  <col2>4</col2>
</cols>

    Noter que pour XML, mise en forme et espace ont sans signification inhérente. Donc, soit vous voulez un VARCHAR résultat avec plusieurs lignes (ressemblant à du XML), ou si vous voulez XML - choisir un.

    • L'OP veut un document XML par ligne je pense.
    • Oui. "résultat Attendu: une seule colonne (xml) et 4 lignes"
    • Bienvenue sur StackOverflow. L'édition n'est pas prévu pour la suppression des réponses que vous pensez manquer le point, et en fait, il ne retirez pas la réponse, il suffit de le rendre encore moins pertinent qu'il ne l'était auparavant.
    • Nice. Si elle pouvait être caché de mon point de vue, il serait mieux que juste de le rendre moins pertinente. Je n'ai pas supprimé les réponses si elles pourraient ajouter de la valeur. Mais ils ne. Ils ont juste méritent leur auteur.
    • Joli coup. sélectionnez résultat = (select macolonne pour xml path) from Matable; est une étrange construction intérieure sélectionnez ne pas avoir de clause from. Il ne fonctionne pas avec * dans la liste de sélection. Est-il vraiment pris en charge par sql server ou est-il un hack ? Cela fonctionne bien. Merci !
    • Je vois que vous en déduire qu'il fonctionne avec *. Great stuff

    InformationsquelleAutor RichardTheKiwi