Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
Ситуация такая: в БД есть 2 таблицы St и KPP: St) StID, pass; KPP) ID, DateTime, Act. Они связаны отношением один ко многим. Есть файл xml с табличными данными(столбцы ID, DateTime, Act). В файле несколько сотен строк. ID периодически повторяются. Нужно данные из этого файла ДОБАВИТЬ во вторую таблицу. Никак не могу додуматься как сделать проверку на уникальность значений ID из xml в StID. Пока есть вот такой код:

DataSet dsid = new DataSet();
            DataTable dtid = dsid.Tables.Add("tableid");
            dtid.Columns.AddRange(new[] { new DataColumn("id", typeof(string)) });

            foreach (XElement elem in doc.Root.Elements())
            {
                dtid.NewRow();
                dtid.Rows.Add(elem.Element("id").Value);
            }

            DataTable daid = dtid.DefaultView.ToTable(true, "id");
            dtid.DefaultView.Table.AcceptChanges();

            SqlDataAdapter daidadap = new SqlDataAdapter();
            daidadap.InsertCommand = new SqlCommand("Insert into St (stid) Values (@id)", conn);
            daidadap.InsertCommand.Parameters.Add("@id", SqlDbType.Char, 40, "studentid");
            daidadap.Update(dsid, "tableid");

            DataSet gds = new DataSet();
            DataTable gdt = gds.Tables.Add("general");
            gdt.Columns.AddRange(new[] { new DataColumn("id", typeof(string)), 
                                         new DataColumn("datetime", typeof(DateTime)), 
                                         new DataColumn("action", typeof(string)) });

            foreach (XElement el in doc.Root.Elements())
            {
                gdt.NewRow();
                gdt.Rows.Add(el.Element("id").Value, el.Element("datetime").Value, el.Element("action").Value);
            }

            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand("Insert into KPP(ID, DateTime, Action) Values (@ID, @DateTime, @Action)", conn);
            da.InsertCommand.Parameters.Add("@id", SqlDbType.Text).SourceColumn = "id";
            da.InsertCommand.Parameters.Add("@datetime", SqlDbType.DateTime).SourceColumn = "datetime";
            da.InsertCommand.Parameters.Add("@action", SqlDbType.Char).SourceColumn = "action";
            da.Update(gds, "general");


прошу помочь с дальнейшей грамотной реализацией, или хотябы указать, в какую сторону дальше копать. Приложение консольное.
11 мар 12, 17:53    [12227092]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
qwerty112
Guest
jarrhead,

процедуру сделай на сервере, в которую будет передаватся текст хмл-я,
и все проверки/вставку - сделать в ней
11 мар 12, 18:09    [12227195]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
а поподробнее? с mssql практически не работал
11 мар 12, 18:11    [12227217]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
qwerty112
Guest
jarrhead
а поподробнее? с mssql практически не работал

пишеш ХП со всей логикой обработки, - openxml или xquery

в программе - читаеш весь текст хмл-я в переменную, и передаеш параметром при вызове ХП
11 мар 12, 18:18    [12227278]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
это вряд ли подойдет. конкретизирую задачу. с удаленных машин на сервер будет пересылаться около 250 xml файлов каждые полчаса. моя задача эти файлы прочитать и загрузить инфу в БД. пока планирую сделать отдельную службу, которая будет следить за определенной папкой, отслеживать появление новых файлов. тренируюсь на консольном приложении.
11 мар 12, 20:38    [12227976]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Не надо тут никакого кода, заливаете через компонент XmlBulkLoad (уже есть в SQL от MS). Нуна только указать связки таблиц и колонок через XSD.
EXEC dbo.spXmlBulkLoad 'Z:\Path\Data.xml', 'Z:\Path\Schema.xsd'
После заливки удаляешь/изменяешь дубли простым запросом.

А ещё прикол в том что, что бы вы не написали обыграть XmlBulkLoad в скорости у вас не получится. В итоге минимум кода, максимум скорости и всё легко сопровождается.
11 мар 12, 20:39    [12227981]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
тут нужна схема. xml-ка формируется из 1С 7.7. без схемы :(
я сам понимаю что задача не особо сложная. но есть только минимум инструментов и возможностей. поэтому и застрял
11 мар 12, 20:46    [12228015]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
iljy
Member

Откуда:
Сообщений: 8711
jarrhead
тут нужна схема. xml-ка формируется из 1С 7.7. без схемы :(
я сам понимаю что задача не особо сложная. но есть только минимум инструментов и возможностей. поэтому и застрял



Эммм... а написать схему самому - религия не позволяет?
11 мар 12, 22:08    [12228271]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31986
jarrhead
тут нужна схема. xml-ка формируется из 1С 7.7. без схемы :(
я сам понимаю что задача не особо сложная. но есть только минимум инструментов и возможностей. поэтому и застрял
В SSIS можно сформировать XSD для загрузки.

Просто делаете таск для импорта XML, выбираете исходный файл, и нажимаете кнопку "сделать схему".

Потом можно использовать как для SSIS, так и для XmlBulkLoad
11 мар 12, 22:34    [12228389]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
спасибо, завтра попробую
11 мар 12, 23:24    [12228639]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
XSD генераторы есть в нете online.
11 мар 12, 23:50    [12228724]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6203
Mnior
Не надо тут никакого кода, заливаете через компонент XmlBulkLoad (уже есть в SQL от MS).

Точнее, SQLXMLBulkLoad.
Mnior
Нуна только указать связки таблиц и колонок через XSD.
EXEC dbo.spXmlBulkLoad 'Z:\Path\Data.xml', 'Z:\Path\Schema.xsd'

Надо бы в таких случаях приписывать, что spXmlBulkLoad - вот эта. А то я сначала весь msdn облазил в поисках описания этой ХП - думаю, я не один такой окажусь.
12 мар 12, 06:34    [12229124]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Сон Веры Павловны
Надо бы в таких случаях приписывать, что spXmlBulkLoad - вот эта.
Ну, эээ, это один из вариантов. А так да, поленился.
12 мар 12, 13:42    [12231287]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
спасибо за подсказку:) метод стоящий:) осмелюсь набраться наглости попросить составить помочь составить схему
мое творение не работает
+

<?xml version="1.0" encoding="utf-16"?>
<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" 
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"            
		   xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
			<xsd:annotation>  
				<xsd:appinfo>    
					<sql:relationship name="ID"          
							         parent="St"          
							         parent-key="ID"          
							         child="KPP"
						                   child-key="ID" />
				 </xsd:appinfo>
			</xsd:annotation>  
			<xsd:element name="St" 
					    sql:relation="KPP" >   
						<xsd:complexType>     
							<xsd:sequence>       
								<xsd:element name="id"  
										      type="xsd:string" />       
								<xsd:element name="datetime" 
										      type="xsd:string" />       
								<xsd:element name="action"        
										      type="xsd:string" />                                     
    
							</xsd:sequence>    
						</xsd:complexType> 
			</xsd:element>
</xsd:schema>

12 мар 12, 18:02    [12234091]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
jarrhead, а нафига вам связка sql:relationship ???
У вас одна единственная табла (KPP). Просто у вас извращение (пока непонятное) в базе - есть некая табла St которая сожержит уникальные идентификаторы из KPP.
Кстати у вас и XML криворукий.

Вам надо один раз тупо залить в KPP. А потом SELECT DISTINCT ID FROM dbo.KPP.

И кстати, мы этот ваш XML в глаза не видели, а читалка мыслей на форуме пока в бета тесте. Так что выкладывайте. Банально названия ROOT элемента неизвестно.
+
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
	<xsd:element name="ROOT" sql:is-constant="1">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="St" sql:relation="dbo.KPP">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="id" type="xsd:integer" sql:field="ID" />
							<xsd:element name="datetime" type="xsd:dateTime" sql:field="DateTime" />
							<xsd:element name="action" type="xsd:string" sql:field="Action" />
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

PS: Если не хотите отгребсти граблей, всегда пишите схему у объектов (в вашем случае dbo.).
12 мар 12, 18:35    [12234350]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
наезд справедлив :) исправляюсь
есть некая БД. в ней пока 2 таблицы. в первой (St) содержатся ID сотрудника и хэш его пароля. ID - primary key. во второй таблице (KPP) содержатся события с проходной. ID сотрудника, дата-время события и само событие(вход/выход). таблицы связаны в отношении "один ко многим". у одного сотрудника может быть много событий. существует несколько проходных, на всех проходных стоит 1С 7.7, которая и регистрирует эти события по электронным ключам сотрудников. каждые полчаса эти проходные формируют XML файл с этими событиями и перекидывают через POST на наш сервер. моя задача прочитать эти xml-ки и перекинуть инфу в MS SQL. беда в том, что в 1С могут завести нового сотрудника, но я об этом узнаю только из самой xml, там появится новый ID. на данный момент я могу прочитать xml файл и закинуть события во вторую таблицу, но как только появляется новый сотрудник вставка данных не происходит, т.к. этого сотрудника нет в первой таблице. пока что я вижу алгоритм решения в том, чтобы проверять каждый ID в пришедшей xml-ке на наличие в первой таблице. но код получается кривой. ежу понятно что это не лучшее решение.
пока поступили предложения реализовать все через сам сервер SQL, через службу на сервере, которая читает xml и перекидывает данные в SQL и через скрипт на странице веб-сайта придприятия, написаный на ASP.NET.

К сообщению приложен файл (kpp.xml - 757bytes) cкачать
13 мар 12, 09:22    [12236318]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
jarrhead, ну дык я и описал вариант, когда ужё после закачки получить новые ID-шки.
Если у вас есть вопрос (который вы боитесь задать): Как это ID после когда стоит FK?
То можно:
1. отколючать FK к примеру и после вставки ставить назат
2. Закачиавть в промежуточную (специально для закачки) таблицу. И после выравнивания данных (добавлении новых пользователей) закачивать в основную.

XML кривой тем, что там не аттрибуты и не типизированный. Вам ещё и парсить время надо будет.

PS: Замените
<xsd:element name="ROOT" sql:is-constant="1">
на
<xsd:element name="UPLOAD" sql:is-constant="1">
Ну типы данных XML вы сами уточните.
13 мар 12, 16:49    [12240917]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
jarrhead
Member

Откуда:
Сообщений: 25
поясните, пожалуйста, что плохого в том, что нет атрибутов? я не вижу разницы в данном конкретном случае. xml очень простой в плане структуры
13 мар 12, 17:29    [12241458]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
jarrhead
поясните, пожалуйста, что плохого в том, что нет атрибутов? я не вижу разницы в данном конкретном случае. xml очень простой в плане структуры
Банально меньше весит, а остальное мелочи - чуть легче читается, и не могут атрибуты дублироваться, в этом-то и смысл атрибутов - свойства. Микроскопом тоже можно гвозди забивать и ни чё так, особенно если приспособиться.
14 мар 12, 23:21    [12249526]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: прошу помощи с импортом данных из xml в mssql  [new]
consider96
Member

Откуда:
Сообщений: 3
Уважаемые Гуру! Помогите пожалуйста с правильным созданием схемы для импорта в MS SQL.

Пример во вложении.

К сообщению приложен файл (9.rar - 1Kb) cкачать
26 май 13, 10:09    [14350283]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
consider96,, скажите честно, зачем вы ставитье RAR сразу после вянды?
Зачем вы создаёте именно RAR архивы?
26 май 13, 14:14    [14350654]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
consider96
Member

Откуда:
Сообщений: 3
ну если это имеет отношение к решению проблемы, то может так?

К сообщению приложен файл (9.zip - 1Kb) cкачать
26 май 13, 16:13    [14350809]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
consider96
Member

Откуда:
Сообщений: 3
PR_4import.xml
<?xml version="1.0" encoding="utf-8"?>
<response>
<track_number id="67807055100298" carrier="EMS">
<count>1</count>
<items>
<item id="1">
<routeStartLoc value="Майя"></routeStartLoc>
<routeStartTime value="15/10/2012 00:00"></routeStartTime>
<routeEndLoc value="Майя"></routeEndLoc>
<routeEndTime value="16/11/2012 00:00"></routeEndTime>
<transitTime value=""></transitTime>
<signedFor value=""></signedFor>
<delivered value=""></delivered>
<tracking>
<track id="0" date="15/10/2012" time="00:00" geo="Майя" event="Приём. Партионный"></track>
<track id="1" date="16/10/2012" time="20:52" geo="Майя" event="Обработка. Прибыло в место вручения"></track>
<track id="2" date="30/10/2012" time="17:27" geo="Майя" event="Неудачная попытка вручения. Временное отсутствие адресата"></track>
<track id="3" date="16/11/2012" time="00:00" geo="Майя" event="Возврат. Истёк срок хранения"></track>
</tracking>
</item>
</items>
</track_number>
</response>


PR_4schema.xml
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

    <ElementType name="routeStartTime" dt:type="string" />
   
   <ElementType name="response" sql:is-constant="1">
      <element type="track_number id" />
      <element type="Count" />
      <element type="items" />
      <element type="item id" />



   </ElementType>

   <ElementType name="track_number id"  sql:relation="Cr_1">
      <element type="routeStartTime"  sql:field="routeStartTime" />
   </ElementType>

</Schema>


- пытаюсь описать соответствия (хотя бы по одному полю), но увы не получается, получаю лог:
error.log
<?xml version="1.0"?><Result State="FAILED"><Error><HResult>0x80004005</HResult><Description><![CDATA[Schema: invalid 'name' on 'ElementType/AttributeType'.]]></Description><Source>Schema mapping</Source><Type>FATAL</Type></Error></Result>


или может стоит делать как то так:
DECLARE @xml XML;
SELECT @xml = CONVERT(xml, BulkColumn, 2) 
FROM OPENROWSET(BULK N'C:\TOOLS\XML2SQL\8\PR_4import.xml', SINGLE_BLOB) as x

Insert Into cr_1(carrier,id1)
SELECT
	
	x.col.value('(track_number)[1]', 'int'),
	x.col.value('tracking[field-name="geo"][1]/field-value[1]', 'NVARCHAR(255)') --AS [Description]
 
FROM
	@xml.nodes('/Data/Rec') x(col)

SELECT * FROM cr_1


здесь не получается у меня выбрать необходимые данные
26 май 13, 16:38    [14350837]     Ответить | Цитировать Сообщить модератору
 Re: прошу помощи с импортом данных из xml в mssql  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ради 20 строк делать прикрепляемый файл - извращение. Яб сказал бы чуть ли не троллинг.
И то что у вас в ZIP не сходится с тем что вы привели выше.
И схема - не схема, а чёрт пойми что.
И вместо то-го чтобы создать нормальную тему для себя подымаете старую - некроманство на форуме запрещено правилами.

Вот простое руководство: 12410359. Практически для 3х летних детей.
Если вы не можете разобраться с этой ерундовиной то вам сюда.

Засим тему предлагаю прикрыть.
26 май 13, 21:27    [14351446]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить