Помогите записать данные из XML в таблицу

ArkadyL
Дата: 07.02.2013 17:21:06
Здравствуйте!
Пытаюсь в первый раз перенести данные из XML в таблицу.
Получилось записать их в переменную @xmlDoc, данные такого примитивного вида:

<?xml version = "1.0" encoding="cp866" standalone="yes"?>
<VFPData>
	<accounts>
		<datetr>2008-04-14T00:00:00</datetr>
		<numtr>8103</numtr>
		<whorefuse></whorefuse>
		<subnumtr>0</subnumtr>
		<sumtr>6520.40</sumtr>
		<sumtr_n>0.00</sumtr_n>
		<cards>2</cards>
		<cards_n>0</cards_n>
	</accounts>
	<accounts>
		<datetr>2008-04-14T00:00:00</datetr>
		<numtr>1797а15</numtr>
		<whorefuse></whorefuse>
		<subnumtr>0</subnumtr>
		<sumtr>7689.26</sumtr>
		<sumtr_n>0.00</sumtr_n>
		<cards>1</cards>
		<cards_n>0</cards_n>
	</accounts>

и т. д.

Такая команда заполняет все поля значениями NULL.
INSERT INTO [Account]
           ([date_account]
           ,[num_account]
           ,[code_Refuse]
           ,[SubNumTr]
           ,[summa]
           ,[summa_Refuse]
           ,[cards]
           ,[cards_Refuse])
     SELECT
           convert(datetime, node.value('@datetr', 'nvarchar(24)'), 126) as date_account  
           ,node.value('@numtr', 'varchar(7)') as num_account
           ,node.value('@whorefuse', 'varchar(7)') AS code_Refuse
           ,node.value('@subnumtr', 'varchar(5)') AS SubNumTr
           ,node.value('@sumtr', 'numeric(12,2)') AS summa
           ,node.value('@sumtr_n', 'numeric(12,2)') AS summa_Refuse
           ,node.value('@cards', 'smallint') AS cards
           ,node.value('@cards_n', 'smallint') AS cards_Refuse
  from @xmlDoc.nodes('VFPData/accounts') as t(node)

Подскажите, пожалуйста, где тут ошибка.
Гость333
Дата: 07.02.2013 17:27:08
ArkadyL,

declare @xmlDoc xml;

set @xmlDoc = '<?xml version = "1.0" encoding="cp866" standalone="yes"?>
<VFPData>
	<accounts>
		<datetr>2008-04-14T00:00:00</datetr>
		<numtr>8103</numtr>
		<whorefuse></whorefuse>
		<subnumtr>0</subnumtr>
		<sumtr>6520.40</sumtr>
		<sumtr_n>0.00</sumtr_n>
		<cards>2</cards>
		<cards_n>0</cards_n>
	</accounts>
	<accounts>
		<datetr>2008-04-14T00:00:00</datetr>
		<numtr>1797а15</numtr>
		<whorefuse></whorefuse>
		<subnumtr>0</subnumtr>
		<sumtr>7689.26</sumtr>
		<sumtr_n>0.00</sumtr_n>
		<cards>1</cards>
		<cards_n>0</cards_n>
	</accounts>
</VFPData>';

     SELECT
           convert(datetime, node.value('datetr[1]', 'nvarchar(24)'), 126) as date_account  
           ,node.value('numtr[1]', 'varchar(7)') as num_account
           ,node.value('whorefuse[1]', 'varchar(7)') AS code_Refuse
           ,node.value('subnumtr[1]', 'varchar(5)') AS SubNumTr
           ,node.value('sumtr[1]', 'numeric(12,2)') AS summa
           ,node.value('sumtr_n[1]', 'numeric(12,2)') AS summa_Refuse
           ,node.value('cards[1]', 'smallint') AS cards
           ,node.value('cards_n[1]', 'smallint') AS cards_Refuse
  from @xmlDoc.nodes('VFPData/accounts') as t(node);
Гость333
Дата: 07.02.2013 17:32:48
ArkadyL,

А ваш запрос сработает для XML такого вида:
declare @xmlDoc xml;

set @xmlDoc = '<?xml version = "1.0" encoding="cp866" standalone="yes"?>
<VFPData>
    <accounts datetr="2008-04-14T00:00:00"
              numtr="8103"
              whorefuse=""
              subnumtr="0"
              sumtr="6520.40"
              sumtr_n="0.00"
              cards="2"
              cards_n="0"
    />
    <accounts datetr="2008-04-14T00:00:00"
              numtr="1797а15"
              whorefuse=""
              subnumtr="0"
              sumtr="7689.26"
              sumtr_n="0.00"
              cards="1"
              cards_n="0"
    />
</VFPData>';

     SELECT
           convert(datetime, node.value('@datetr', 'nvarchar(24)'), 126) as date_account  
           ,node.value('@numtr', 'varchar(7)') as num_account
           ,node.value('@whorefuse', 'varchar(7)') AS code_Refuse
           ,node.value('@subnumtr', 'varchar(5)') AS SubNumTr
           ,node.value('@sumtr', 'numeric(12,2)') AS summa
           ,node.value('@sumtr_n', 'numeric(12,2)') AS summa_Refuse
           ,node.value('@cards', 'smallint') AS cards
           ,node.value('@cards_n', 'smallint') AS cards_Refuse
  from @xmlDoc.nodes('VFPData/accounts') as t(node);
ArkadyL
Дата: 07.02.2013 18:32:16
Спасибо. Вообще-то сработал и первый вариант.
На самом деле я обрезал реальный XML, в котором полей и записей гораздо больше, а у меня генерируется именно такая структура. Единственное, если в столбце даты нет ни одного значения, он целиком заполняется NULL, а если есть где-нибудь хоть одно - в пустых ставится 1900-01-01.
Всё это затеяно ради MEMO-полей, которые не записать в csv-файл. А нет ли быстрой загрузки такого XML-файла типа BULK INSERT без INSERT .... SELECT?
Гость333
Дата: 08.02.2013 10:17:51
ArkadyL
А нет ли быстрой загрузки такого XML-файла типа BULK INSERT без INSERT .... SELECT?

Выполнение массовой загрузки XML-данных (SQLXML 4.0)
Простой пример: Использование массовой загрузки SQLXML в среде .NET
Если будете компилировать пример под .NET 4.0, то "SQLXMLBulkLoad4Class" замените на "SQLXMLBulkLoad4".
user89
Дата: 11.02.2013 11:31:34
ArkadyL
А нет ли быстрой загрузки такого XML-файла типа BULK INSERT без INSERT .... SELECT?
Вариантик
declare @x xml
select @x = BulkColumn from openrowset(
bulk 'c:\export_off_203.xml', single_blob) as x
ArkadyL
Дата: 11.02.2013 15:46:26
Так этим создаётся ведь XML-переменная, которую надо разбирать, чем я и занимался в первом сообщении. Или нет?
Гость333
Дата: 11.02.2013 15:48:44
ArkadyL
Так этим создаётся ведь XML-переменная, которую надо разбирать, чем я и занимался в первом сообщении. Или нет?

Да. Видимо, коллега user89 не до конца понял, что нужно сделать.
С SQLXMLBulkLoad что-нибудь получилось?
ArkadyL
Дата: 11.02.2013 16:47:09
Пока не пробовал, там надо создавать всякие XSD-схемы вручную, боюсь, у меня много времени уйдёт на эксперименты. Ваш первый вариант меня сейчас очень даже устраивает.
ArkadyL
Дата: 22.02.2013 16:10:20
Попробовал воспользоваться XMLBulkLoad

objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = 'Driver={SQL Native Client};Server=S01-7800-FS01\DB;Database=DB;Trusted_Connection=yes'
objBL.ErrorLogFile = "c:\program1\error.log"
objBL.Execute("file.xml", "file.xsd")


Получилась ошибка

<?xml version="1.0"?>
<Result State="FAILED">
<Error>
<HResult>0x80040E21I32</HResult>
<Description><![CDATA[Error creating a session.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error></Resu


Подскажите, пожалуйста, чём может быть проблема?