Форум программистов [Powered by Invision Power Board]

Код


-<licenses_list>
-<licenses>
<name>Имя</name>
<activity_type>Деятельность</activity_type>
<full_name_licensee>Длинное имя</full_name_licensee>
<address>Адрес</address>
 
-<work_address_list>
-<address_place>
<address>Другой адрес</address>
<index>Индекс</index>
<region>Регион</region>
<city>Город</city>
 
-<works>
<work>Услуги 1</work>
<work>Услуги 2</work>
</works>
</address_place>
-<address_place>
<address>Другой адрес</address>
<index>Индекс</index>
<region>Регион</region>
<city>Город</city>
 
-<works>
<work>Услуги 1</work>
<work>Услуги 2</work>
</works>
</address_place>
 

-</licenses>
-</licenses_list>

Код


DECLARE @S VARCHAR(MAX);
DECLARE @xml xml;
SELECT @xml = 
CONVERT(xml, BulkColumn, 2) 
FROM OPENROWSET(BULK 'адрес.xml', SINGLE_BLOB) AS x
SET @S = '<?xml version="1.0" encoding="windows-1251"?>' + CONVERT(VARCHAR(MAX),@xml)
 
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @S
 
TRUNCATE TABLE [dbo].[test1]
INSERT INTO [dbo].[test1]
    ([name]
      ,[activity_type]
      ,[full_name_licensee]
      ,[address]
      ,[INDEX]
      ,[region]
      ,[city]
      ,[WORK]
SELECT
     CASE 
       WHEN name = '' THEN NULL
       ELSE name
     END name
      ,CASE 
       WHEN activity_type = '' THEN NULL
       ELSE activity_type
     END activity_type
      ,CASE 
       WHEN full_name_licensee = '' THEN NULL
       ELSE full_name_licensee
     END full_name_licensee
 ,CASE 
       WHEN address = '' THEN NULL
       ELSE address
     END address
,CASE 
       WHEN [INDEX] = '' THEN NULL
       ELSE [INDEX]
     END [INDEX]
      ,CASE 
       WHEN region = '' THEN NULL
       ELSE region
     END region
     ,CASE 
       WHEN city = '' THEN NULL
       ELSE city
     END city
,CASE 
       WHEN [WORK] = '' THEN NULL
       ELSE [WORK]
     END [WORK]
FROM OPENXML (@idoc,'/licenses_list/licenses')
WITH ([name] [VARCHAR](128) 'name',
    [activity_type] [VARCHAR](128) 'activity_type',
    [full_name_licensee] [VARCHAR](254) 'full_name_licensee',
[address] [VARCHAR](254) 'address',
    [INDEX] [INT] 'index',
    [region] [VARCHAR](128) 'region',
    [city] [VARCHAR](128) 'city',
    [WORK] [VARCHAR](254) 'work'