Is it possible to export my SQL replication definition to XML?

Question

I was asked to do this, but I don't know if there is a standard way of doing so. Does anyone know if there is a way in SQL Server to export this?

Answer 1

AFIK there is no standard way of doing this. Replication as a technology long predates XML and its toolset and metadata is not XML centric. However everything related to replication is stored somewhere in a table, either in master or in msdb or in distribution database, see Replication Tables topic on MSDN, or in the replication DMVs (sys.dm_repl_articles and sys.dm_repl_schemas). All this information can be interogated and formatted as XML, but I'm not aware of any standard XML schemas covering this information.

Answer 2

Remus' answer worked great. Here is the SQL script I used to export this to XML. Hopefully it will be useful to someone else:

DECLARE @PublicationId INT
SET @PublicationId = 1 –- Use your publication ID here. Use SELECT * FROM syspublications to see a list of publications

select
      Publication.name AS [Name]
      , Publication.description AS [Description]
      , Article.name AS [Name]
      , Article.dest_table AS [Table]
      , [Column].name AS [Name]
      , [Column].[Type]
      , [Column].MaxLength
      , [Column].Nullable
from dbo.syspublications Publication
      join dbo.sysarticles Article on Publication.pubid = Article.pubid
            join sys.tables st on st.object_id = Article.objid
      join dbo.sysarticlecolumns ac on Article.artid = ac.artid
      join 
      (
            select
                  sc.object_id
                  , sc.column_id
                  , sc.name AS [Name]
                  , sty.name AS [Type]
                  , sc.max_length AS MaxLength
                  , sc.is_nullable AS Nullable
            from dbo.syspublications p
                  join dbo.sysarticles a on p.pubid = a.pubid
                  join dbo.sysarticlecolumns ac on a.artid = ac.artid
                        join sys.columns sc on sc.object_id = a.objid AND sc.column_id = ac.colid
                  join sys.types sty on sty.user_type_id = sc.user_type_id
            where p.pubid = @PublicationId
      ) [Column] on [Column].object_id = Article.objid AND [Column].column_id = ac.colid
where Publication.pubid = @PublicationId FOR XML AUTO