2-НДФЛ в XML своими руками

SET NOCOUNT ON

declare @filename NVARCHAR(255)
declare @xml XML
declare @xmlstr nvarchar(max)
DECLARE @FileFolder NVARCHAR(128)

SET @FileFolder = N’C:\OUT2NDFL\’ /* Папка на сервере SQL */

SET @xml = (
SELECT
N’NO_NDFL2_’ + BranchTaxOffice + ‘_’ + BranchTaxOffice + ‘_’ + CompanyINN + CompanyKPP + ‘_’ +
CONVERT(NCHAR(8), GETDATE(), 112) + ‘_’ + CONVERT(NCHAR(36), NEWID()) AS [@ИдФайл],
N’ЭОН 4.28.4′ AS [@ВерсПрог],
N’5.02′ AS [@ВерсФорм],
BranchOKATO AS [СвРекв/@ОКАТО],
N’2012′ AS [СвРекв/@ОтчетГод],
N’1′ AS [СвРекв/@ПризнакФ],
CompanyINN AS [СвРекв/СвЮЛ/@ИННЮЛ],
CompanyKPP AS [СвРекв/СвЮЛ/@КПП],
( SELECT TOP 10
N’1151078′ AS [@КНД],
CONVERT(NCHAR(10), GETDATE(), 104) AS [@ДатаДок],
N’2012′ AS [@ОтчетГод],
RTRIM(PA04001) as [@НомСпр],
N’1′ AS [@Признак],
BranchTaxOffice AS [@КодНО],
BranchOKATO AS [СвНА/@ОКАТО],
N’(495) 9370700′ AS [СвНА/@Тлф],
BranchFullName AS [СвНА/СвНАЮЛ/@НаимОрг],
CompanyINN AS [СвНА/СвНАЮЛ/@ИННЮЛ],
CompanyKPP AS [СвНА/СвНАЮЛ/@КПП],
NULLIF(RTRIM(PA04048), SPACE(0)) AS [ПолучДох/@ИННФЛ],
N’1′ AS [ПолучДох/@Статус],
CONVERT(NCHAR(10), CONVERT(DATETIME, PA04072 , 112), 104) AS [ПолучДох/@ДатаРожд],
N’643′ AS [ПолучДох/@Гражд],
RTRIM(UPPER(LEFT(PA04045, 1)) + LOWER(SUBSTRING(PA04045, 2, 50))) AS [ПолучДох/ФИО/@Фамилия],
RTRIM(UPPER(LEFT(PA04046, 1)) + LOWER(SUBSTRING(PA04046, 2, 50))) AS [ПолучДох/ФИО/@Имя],
NULLIF(RTRIM(UPPER(LEFT(PA04047, 1)) + LOWER(SUBSTRING(PA04047, 2, 50))), SPACE(0)) AS [ПолучДох/ФИО/@Отчество],
RTRIM(PA04029) AS [ПолучДох/УдЛичнФЛ/@КодУдЛичн],
NULLIF(RTRIM(PA04059), SPACE(0)) AS [ПолучДох/УдЛичнФЛ/@СерНомДок]
/* <АдрРФ> */
, RTRIM(NULLIF(pa11_70.PA11004, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Индекс]
, RTRIM(PA04009) as [ПолучДох/АдрМЖРФ/@КодРегион]
, RTRIM(NULLIF(pa11_70.PA11005, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Район]
, RTRIM(NULLIF(pa11_70.PA11006, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Город]
, RTRIM(NULLIF(pa11_71.PA11004, SPACE(0))) as [ПолучДох/АдрМЖРФ/@НаселПункт]
, RTRIM(NULLIF(pa11_71.PA11005, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Улица]
, RTRIM(NULLIF(pa11_71.PA11006, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Дом]
, RTRIM(NULLIF(pa11_72.PA11004, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Корпус]
, RTRIM(NULLIF(pa11_72.PA11005, SPACE(0))) as [ПолучДох/АдрМЖРФ/@Кварт],
/* </АдрРФ> */
N’13’ AS [СведДох/@Ставка],

(
SELECT
N’01’ AS [СвСумДох/@Месяц],
N’2000′ AS [СвСумДох/@КодДоход],
N’1000.00′ AS [СвСумДох/@СумДоход]
FOR XML PATH(»), TYPE
) AS [СведДох/ДохВыч],

N’1000.00′ AS [СведДох/СГДНалПер/@СумДохОбщ],
N’1000.00′ AS [СведДох/СГДНалПер/@НалБаза],

N’130′ AS [СведДох/СГДНалПер/@НалИсчисл],
N’130′ AS [СведДох/СГДНалПер/@НалУдерж],
N’130′ AS [СведДох/СГДНалПер/@НалПеречисл],

N’0′ AS [СведДох/СГДНалПер/@НалУдержЛиш],
N’0′ AS [СведДох/СГДНалПер/@НалНеУдерж]

FROM PA040100 pa04 (NOLOCK)
left join PA110100 pa11_70 (NOLOCK)
on PA04001 = pa11_70.PA11001 and pa11_70.PA11002 = N’70’
left join PA110100 pa11_71 (NOLOCK)
on PA04001 = pa11_71.PA11001 and pa11_71.PA11002 = N’71’
left join PA110100 pa11_72 (NOLOCK)
on PA04001 = pa11_72.PA11001 and pa11_72.PA11002 = N’72’

WHERE PA04001 IN (SELECT PA06001 FROM PA060100 WHERE YEAR(PA06002) = 2012)
ORDER BY PA04001
FOR XML PATH(’Документ’), TYPE
)
FROM ( SELECT
N’3535′ AS BranchTaxOffice, /* Код налогового органа */
N’3535000012′ AS CompanyINN, /* ИНН компании */
N’353500001′ AS CompanyKPP, /* КПП компании */
N’19401000000′ AS BranchOKATO, /* ОКАТО */
N’ООО "Моя компания"’ AS BranchFullName /* Название компании */
) AS ScaCompanyParams
FOR XML PATH(’Файл’), ELEMENTS XSINIL
);

SET @filename = @FileFolder + @xml.value(’(/Файл/@ИдФайл)[1]’, ‘nchar(84)’) + ‘.xml’
SET @xmlstr = CONVERT(NVARCHAR(MAX), @xml)

/* Запись файла в формате XML */
EXEC iScalaRPT.dbo.writetoxmlfile1251 @xmlstr, @filename

SELECT @filename

Public Class SQLCLRIO
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub WriteToXMLFile1251(ByVal content As String, _
                                  ByVal filename As String)

        Try
           Dim ascii As Encoding = Encoding.GetEncoding(1251)
           File.WriteAllText(filename, "<?xml version=""1.0"" encoding=""windows-1251""?>", ascii)
           File.AppendAllText(filename, content, ascii)
        Catch ex As Exception
            SqlContext.Pipe.Send("Error writing to file " & ex.Message)
        End Try

    End Sub

End Class

exec sp_configure ‘clr enabled’,1

reconfigure
go

alter database ScalaDB set trustworthy on
go

use ScalaDB
go

create assembly WriteToXMLFile1251 from ‘C:\CustomDLL\WriteToXMLFile1251.dll’
with permission_set = external_access
GO

create procedure dbo.writetoxmlfile1251
(
   @content nvarchar(max),
   @filename nvarchar(255)
)
as external name WriteToXMLFile1251.SQLCLRIO.WriteToXMLFile1251

GO

Set objArgs = WScript.Arguments
s = objArgs(0)

If s = "" Then
                wscript.echo "Не указано имя файла!"
                wscript.quit
End If

Dim TesterRootFolder
Dim parser

TesterRootFolder = "C:\Tester"

    Set parser = CreateObject("Parser2NDFLM.TParser2NDFLM")

With parser
    .Filepath = s
    .IniFile = TesterRootFolder & "\Parser2NDFL\Parser2NDFL2011.INI"
    .KLADRPath = TesterRootFolder & "\Kladr\"
    .StoragePath = TesterRootFolder & "\Parser2NDFL\Storage\"
    .SprPath = TesterRootFolder & "\Parser2NDFL\Spr\"
    .LogPath = TesterRootFolder & "\Parser2NDFL\Output\"
    .ParserLogPath = TesterRootFolder & "\Parser2NDFL\Output\"
    .CreateStorage = True
    .CreateXMLLog = True
    .ShowCount = 10
    .ShowProcess = False
    .CheckAddress = True
    .AdrWarningsAsErrors = False
    .DebugInfo = True
   
    If .ParseFile Then
        MsgBox ("Файл успешно принят")
    Else
        MsgBox ("Файл не принят")
    End If
End With

    Set parser = Nothing