Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Доброе утро.

Можете помочь или подсказать, как перелить данные из конкретной xml в скуль? Раньше при необходимости работы с xml (не большие), разбирал по тегам. На данный момент образовалась задача обрабатывать слишком большие файлы и в слишком большом разнообразии - разбивка по тегам и роспись каждого - смертоубийство.
Примеров в инете и на форуме уйма, но не понимаю, как мне мою xml-ку обработать=( Необходимо реализовать с помощью хранимок. Есть схема xsd. Пример xml прикрепил. Вот ссылка на схему https://yadi.sk/d/kjsrnDiHkEoWJ
Буду благодарен любой помощи или правильному направлению, желательно подробному=(

Инфа о скуле - подойдет решение для любого скуля от 2005 до 2012.

К сообщению приложен файл (test.xml - 33Kb) cкачать
5 ноя 15, 09:18    [18371480]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Больше даже интересует - как мне инфу из xml перелить в более менее удобные таблицы для дальнейшей обработки.
5 ноя 15, 09:29    [18371523]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
OPENXML
XQuery
SQLXML
5 ноя 15, 09:39    [18371560]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6203
SQLBeginner2014
На данный момент образовалась задача обрабатывать слишком большие файлы

SqlXmlBulkLoad.
5 ноя 15, 09:44    [18371594]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
aleks2
Guest
SQLBeginner2014
Раньше при необходимости работы с xml (не большие), разбирал по тегам. На данный момент образовалась задача обрабатывать слишком большие файлы и в слишком большом разнообразии - разбивка по тегам и роспись каждого - смертоубийство.

Ты не поверишь, универсального загрузчика xml не существует.
Навроде как не существует универсальной таблицы.

Так что писать, один хрен, придется.
5 ноя 15, 10:36    [18371887]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
aleks2,

знаю, обработку примерно вот так реализовывал раньше:

--временная таблица-шаблон для инфы о контакте
create table #contactPerson (
	id int identity(1,1)
	,lastName nvarchar(max)
	,firstName nvarchar(max)
	,middleName nvarchar(max)
)

DECLARE @idoc int, @doc nvarchar(max); 
SELECT @doc = BulkColumn FROM OPENROWSET(BULK 'D:\testxml\test.xml',SINGLE_CLOB) AS x 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 
--попытка по шаблону
SELECT *
FROM   OPENXML (@idoc, 'root/purchaseResponsible/responsibleInfo/contactPerson') with #contactPerson
--почему не получается по шаблону
SELECT *
FROM   OPENXML (@idoc, 'root/purchaseResponsible/responsibleInfo/contactPerson')
--//почему не получается по шаблону

--обработка по тегам
SELECT id,localname
into #locNames
FROM   OPENXML (@idoc, 'root/purchaseResponsible/responsibleInfo/contactPerson')
where nodetype = 1

SELECT parentid,[text]
into #locValues
FROM   OPENXML (@idoc, 'root/purchaseResponsible/responsibleInfo/contactPerson')
where nodetype = 3

insert #contactPerson
select 
	(select [text] from #locNames ln join #locValues lv on ln.id = lv.parentid where localName = 'lastName')
	,(select [text] from #locNames ln join #locValues lv on ln.id = lv.parentid where localName = 'firstName')
	,(select [text] from #locNames ln join #locValues lv on ln.id = lv.parentid where localName = 'middleName')

select * from #contactPerson
--//обработка по тегам
EXEC sp_xml_removedocument @idoc

drop table #contactPerson,#locNames,#locValues


Но это только по одному паренту. А есть способ насоздавать таблиц по имеющейся схеме? Чтобы можно было уже что-то готовое доделать руками, ибо создавать временные таблицы только по одному документу - поход в чистилище, а по 50-60 видам документов - прогулка по всем кругам ада.

И по OPENXML вопрос - в приведенном запросе получение инфы по шаблону таблицы - это я криворукий и что-то не так сделал, или все дело в xml-ке? Немного преобразованный файл для проверки запроса прикрепил.

К сообщению приложен файл (test.xml - 28Kb) cкачать
5 ноя 15, 11:25    [18372138]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
declare @a nvarchar(4000)
set @a = N'<root>
        <id>21350</id>
        <purchaseNumber>0191100009614000003</purchaseNumber>
        <docPublishDate>2014-01-24T16:27:14.572+10:00</docPublishDate>
        <href>http://zakupki.gov.ru/epz/order/notice/ea44/view/common-info.html?noticeId=21350</href>
        <printForm>
            <url>http://zakupki.gov.ru/epz/order/notice/printForm/view.html?noticeId=21350</url>
        </printForm>
        <purchaseObjectInfo>Поставка канцелярских товаров для нужд Управления Федеральной службы государственной регистрации, кадастра и картографии по Забайкальскому краю</purchaseObjectInfo>
        <purchaseResponsible>
            <responsibleOrg>
                <regNum>01911000096</regNum>
                <fullName>Управление Федеральной службы государственной регистрации, кадастра и картографии по Забайкальскому краю</fullName>
                <postAddress>Российская Федерация, 672002, Забайкальский край, Чита г, Анохина, 63, -</postAddress>
                <factAddress>Российская Федерация, 672002, Забайкальский край, Чита г, Анохина, 63, -</factAddress>
                <INN>7536057403</INN>
                <KPP>753601001</KPP>
            </responsibleOrg>
            <responsibleRole>CU</responsibleRole>
            <responsibleInfo>
                <orgPostAddress>Российская Федерация, 672002, Забайкальский край, Чита г, Анохина, 63, -</orgPostAddress>
                <orgFactAddress>Российская Федерация, 672002, Забайкальский край, Чита г, Анохина, 63, -</orgFactAddress>
                <contactPerson>
                    <lastName>Степанова</lastName>
                    <firstName>Руфина</firstName>
                    <middleName>Викторовна</middleName>
                </contactPerson>
                <contactEMail>tender@zabfrs.ru</contactEMail>
                <contactPhone>8-3022-321216</contactPhone>
                <contactFax>8-3022-321216</contactFax>
            </responsibleInfo>
        </purchaseResponsible>
    </root>'
 
 select @a

create table #contactPerson (
	id int identity(1,1)
	,lastName nvarchar(400)
	,firstName nvarchar(400)
	,middleName nvarchar(400)
)
 
DECLARE @idoc int; 
EXEC sp_xml_preparedocument @idoc OUTPUT, @a; 
--попытка по шаблону
SELECT *
FROM   OPENXML (@idoc, 'root/purchaseResponsible/responsibleInfo/contactPerson',2) with #contactPerson

EXEC sp_xml_removedocument @idoc
drop table #contactPerson
5 ноя 15, 11:54    [18372314]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

в который раз - огромное спасибо!)

можете еще подсказать по SqlXmlBulkLoad - как писали люди выше попробовал и дошел до момента, когда скуль ругнулся вот так:

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure spXMLBulkLoad, Line 47
Ошибка при выполнении метода "Execute" в "D:\testxml\XMLBulkError.xml": Schema: relationship expected on 'printForm'. 


Это из-за того, что в схеме нужно прописывать соответствия "тег-столбец"? Т.е. создавать таблицы в любом случае придется руками?
5 ноя 15, 12:10    [18372452]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
Это из-за того, что в схеме нужно прописывать соответствия "тег-столбец"?

Это из-за того, что никто не видит, что вы вообще там написали.
И откуда черпали знания по составлению схемы.
5 ноя 15, 12:26    [18372608]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
aleks2
Guest
SQLBeginner2014
Glory,

в который раз - огромное спасибо!)

можете еще подсказать по SqlXmlBulkLoad - как писали люди выше попробовал и дошел до момента, когда скуль ругнулся вот так:

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure spXMLBulkLoad, Line 47
Ошибка при выполнении метода "Execute" в "D:\testxml\XMLBulkError.xml": Schema: relationship expected on 'printForm'. 


Это из-за того, что в схеме нужно прописывать соответствия "тег-столбец"? Т.е. создавать таблицы в любом случае придется руками?


https://msdn.microsoft.com/ru-ru/library/ms171806(v=sql.120).aspx

Если опишите схему - таблицы SqlXmlBulkLoad может и сам создавать.
5 ноя 15, 12:33    [18372656]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
aleks2
Guest
Хотя... попробовал я создать и обломался.
Мож кто наставит на путь истинный?

 <?xml version="1.0" encoding="utf-8" ?> 
- <selena xmlns="http://project.selena-online.ru" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://project.selena-online.ru https://selena-online.ru/static/rest/apartmentprice.xsd">
- <apartmentprices>
- <apartment id="26">
<price id="922" tourid="292" price_c3="6000.00" price_c2="4500.00" price_m5="16800.00" price_m3="16800.00" price_m4="16800.00" />
</apartment>
- <apartment id="26">
<price id="923" tourid="293" price_c3="11000.00" price_c2="8300.00" price_m5="39300.00" price_m3="39300.00" price_m4="39300.00" />
</apartment>
- <apartment id="26">
<price id="924" tourid="295" price_c3="6000.00" price_c2="4500.00" price_m5="22300.00" price_m3="22300.00" price_m4="22300.00" />
</apartment>
- <apartment id="26">
<price id="925" tourid="296" price_c3="3000.00" price_c2="2300.00" price_m5="12400.00" price_m3="12400.00" price_m4="12400.00" />
</apartment>
</apartmentprices>
</selena>

- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

- <xsd:annotation>
- <xsd:appinfo>
<sql:relationship name="ApartmentAP" parent="apartment" parent-key="ID" child="price" child-key="ID" />
</xsd:appinfo>
</xsd:annotation>

- <xsd:element name="apartment" sql:relation="Apartment" sql:key-fields="ID">

- <xsd:complexType>
- <xsd:sequence>
- <xsd:element name="price" sql:relation="price" sql:key-fields="ID" sql:relationship="ApartmentAP">
- <xsd:complexType>
<xsd:attribute name="tourid" type="xsd:int" />
<xsd:attribute name="id" type="xsd:int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:int" />
</xsd:complexType>

</xsd:element>

</xsd:schema>

E:\SQL Data>cscript xmlbulkload.vbs
Сервер сценариев Windows (Microsoft R) версия 5.6
c Корпорация Майкрософт (Microsoft Corp.), 1996-2001. Все права защищены.

E:\SQL Data\xmlbulkload.vbs(11, 1) Schema mapping: Schema: the parent/child table of the relationship on 'price' does not match.
5 ноя 15, 12:54    [18372802]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

прошу прощения за неточную инфу, подставил в запрос правильные файлы и ошибок нет. В прочем как и какого-то результата.

Хранимка (взята тут):

CREATE PROCEDURE [dbo].[spXMLBulkLoad] (
	 @@File		SysName
	,@@Schema	SysName
	,@@DataBase	SysName	= 'test'
) AS BEGIN 
	 EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
	DECLARE	 @ErrCode	Int
		,@OLEXMLBulk	Int
		,@ErrMethod	SysName
		,@ErrSource	SysName
		,@ErrDescript	NVarChar(4000)
	SET @ErrSource = 'D:\testxml\XMLBulkError.xml'
	EXEC @ErrCode = sys.sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload' ,@OLEXMLBulk OUT
	IF (@ErrCode = 0) BEGIN
		SET	@@DataBase	= 'Provider=SQLOLEDB;Data Source=test;DataBase=' + @@DataBase + ';Integrated Security=SSPI'
		EXEC @ErrCode = sys.sp_OASetProperty	@OLEXMLBulk ,'ConnectionString'	,@@DataBase		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'ConnectionString'	GOTO Error END
		EXEC @ErrCode = sys.sp_OASetProperty	@OLEXMLBulk ,'ErrorLogFile'	,@ErrSource		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'ErrorLogFile'	GOTO Error END
		EXEC @ErrCode = sys.sp_OASetProperty	@OLEXMLBulk ,'CheckConstraints'	,1			IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'CheckConstraints'	GOTO Error END
		EXEC @ErrCode = sys.sp_OAMethod		@OLEXMLBulk ,'Execute', NULL	,@@Schema, @@File
		IF (@ErrCode != 0) BEGIN
			SET @ErrMethod = 'Execute'
			DECLARE	 @Exist	Int
				,@Error	XML
			EXEC	master.dbo.xp_FileExist	@ErrSource, @Exist OUT
			IF (@Exist = 1) BEGIN
				SELECT	 @Error		= E.Error
							-- Обход глюка
							+ CASE	WHEN Right(E.Error,1) != '>'	THEN 'lt>'
								ELSE ''				END
				FROM	OPENROWSET(BULK 'D:\testxml\XMLBulkError.xml',SINGLE_NCLOB)E(Error) 
				SELECT	@ErrDescript	= IsNull(@ErrDescript	,'') + E.Error.value('Description[1]','SysName') + ' '
				FROM	@Error.nodes('/Result/Error')E(Error)
				SELECT	@ErrDescript	= IsNull(@ErrDescript	,'') + E.Error.value('Description[1]','SysName') + ' '
				FROM	@Error.nodes('/Error/Record')E(Error)
			END ELSE
				GOTO Error
		END
		GOTO Destroy
	Error:	EXEC @ErrCode = sys.sp_OAGetErrorInfo	@OLEXMLBulk ,@ErrSource OUT ,@ErrDescript OUT
	Destroy:EXEC @ErrCode = sys.sp_OADestroy	@OLEXMLBulk
	END ELSE
		SELECT	 @ErrMethod	= 'SQLXMLBulkLoad.SQLXMLBulkload'
			,@ErrSource	= 'sp_OACreate'
			,@ErrDescript	= 'Ошибка создания OLE объекта'
	-- Вывод ошибок
	IF (@ErrMethod IS NOT NULL) BEGIN
		RAISERROR('Ошибка при выполнении метода "%s" в "%s": %s',18,1,@ErrMethod,@ErrSource,@ErrDescript)
		RETURN	@@Error
	END
END


вызов и результат:
EXEC dbo.spXmlBulkLoad 'D:\testxml\testxml.xml','D:\testxml\IntegrationTypes.xsd','test'

Command(s) completed successfully.


А где результат-то посмотреть?

Схема https://yadi.sk/d/kjsrnDiHkEoWJ - взята с офф.сайта закупок
xml прикрепил.

К сообщению приложен файл (testXml.xml - 8Kb) cкачать
5 ноя 15, 13:01    [18372841]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
А где результат-то посмотреть?

Там, куда вы соединилсь при помощи
SET @@DataBase = 'Provider=SQLOLEDB;Data Source=test;DataBase=' + @@DataBase + ';Integrated Security=SSPI'
5 ноя 15, 13:09    [18372880]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

В базе test, вот только не понимаю - где именно? в таблицах и вьюхах ничего нет. Нужно где-то еще посмотреть или нужно что-то где-то дописать?
5 ноя 15, 13:23    [18373012]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
В базе test, вот только не понимаю - где именно? в таблицах и вьюхах ничего нет

Там, где вы указали в своей схеме
5 ноя 15, 13:25    [18373029]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

можно пример? вот начало схемы:

<?xml version="1.0" encoding="UTF-8"?>
<!-- FCS INTEGRATION_TYPES Integration Scheme, version 5.2, create date 26.06.2015 -->
<xs:schema xmlns="http://zakupki.gov.ru/oos/types/1" xmlns:oos="http://zakupki.gov.ru/oos/types/1" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://zakupki.gov.ru/oos/types/1" elementFormDefault="qualified" attributeFormDefault="unqualified">
	<xs:complexType name="account">
		<xs:annotation>
			<xs:documentation>Банковский cчет организации</xs:documentation>
		</xs:annotation>
		<xs:sequence>
			<xs:element name="bankAddress">
				<xs:annotation>
					<xs:documentation>Адрес кредитной организации</xs:documentation>
				</xs:annotation>
				<xs:simpleType>
					<xs:restriction base="xs:string">
						<xs:maxLength value="2000"/>
					</xs:restriction>
				</xs:simpleType>
			</xs:element>


что нужно прописать? или что нужно добавить? не догоняю=(
5 ноя 15, 13:35    [18373116]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
можно пример? вот начало схемы:

Вы ссылку на хелп открывали ?

1. Создайте такую таблицу:
CREATE TABLE Cust(CustomerID int PRIMARY KEY,
CompanyName varchar(20),
City varchar(20));
GO
В текстовом редакторе или редакторе XML по своему выбору создайте файл и сохраните его под именем SampleSchema.xml.Добавьте в этот файл следующую схему XSD.

<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="Customers" sql:relation="Cust" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
5 ноя 15, 13:38    [18373140]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

да, открывал и, собственно,
Glory
1. Создайте такую таблицу

как я понял - в любом случае, нужно сначала таблиц насоздавать самому.

Но при этом:

aleks2
Если опишите схему - таблицы SqlXmlBulkLoad может и сам создавать.


как описать?
Я запутался. В итоге - мне нужно самому насоздаваться таблиц и потом в схеме прописать в какие именно таблицы лить или нужно как-то схему доработать, чтобы таблицы сами создавались?
5 ноя 15, 13:48    [18373235]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
как описать?

Выделил же жирным шрифтом
5 ноя 15, 13:53    [18373281]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

это будет работать если у меня уже есть созданная таблица Cust. А если у меня ее нет? SqlXmlBulkLoad может создавать таблицы сам? На том же примере из хелпа - чтобы после
exec SqlXmlBulkLoad '...', '...', '...'
у меня появилась таблица Customers - так можно сделать? Или в любом случае, мне нужно будет создавать изначально таблицу Cust?
5 ноя 15, 14:00    [18373333]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
это будет работать если у меня уже есть созданная таблица Cust. А если у меня ее нет?

Что мешает проверить то ?
5 ноя 15, 14:02    [18373348]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,


Создал схему, создал xml, попытался выполнить, вот результат:
Msg 50000, Level 18, State 1, Procedure spXMLBulkLoad, Line 48
Ошибка при выполнении метода "Execute" в "D:\testxml\XMLBulkError.xml": Invalid object name 'Cust'. 

т.е. в любом случае нужно создать таблицу, так?
5 ноя 15, 14:07    [18373401]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
т.е. в любом случае нужно создать таблицу, так?

В любом случе кто-то не может даже дочитать ссылку до конца. Даже если его тыкают в нее несколько раз.
5 ноя 15, 14:11    [18373437]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Glory,

Блин, не догоняю. Почитал msdn, во всех примерах нужно создавать таблицы. Наведите. пожалуйста, на путь истинный.
5 ноя 15, 14:35    [18373675]     Ответить | Цитировать Сообщить модератору
 Re: Обработка XML  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQLBeginner2014
Glory,

Блин, не догоняю. Почитал msdn, во всех примерах нужно создавать таблицы. Наведите. пожалуйста, на путь истинный.

Ага, во всех. Особенно в примере "Д.
Формирование схем таблиц перед массовой загрузкой
При необходимости массовая загрузка XML может создать таблицы, если они не существовали до начала массовой загрузки.."
5 ноя 15, 14:37    [18373696]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить