Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Insert load XML- file to SQL  [new]
KIRCOMS
Member

Откуда:
Сообщений: 74
Добрый день

Имеется файл XML со следующей структурой

[SRC XML]<FLK_P xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FNAME> значение </FNAME>
<FNAME_I> значение</FNAME_I>
<PR>
<OSHIB>xxx</OSHIB>
<IM_POL>yyyyy</IM_POL>
<BAS_EL>xyxyxy</BAS_EL>
<N_ZAP>1111111</N_ZAP>
<COMMENT>бла-бла-бла</COMMENT>
<FATALITY>0</FATALITY>
</PR>
[/SRC]

Пытаюсь загрузить следующим SQL запросом

IF OBJECT_ID('tempdb.dbo.табличка) IS NOT NULL DROP TABLE табличка
CREATE TABLE табличка (
RN bigint IDENTITY (1,1) ,
FNAME varchar(24),
Fname_i varchar(24),
OSHIB varchar(3),
IN_POL varchar(20),
BAS_EL varchar(20),
N_ZAP varchar(4),
IDCASE varchar(8),
IDSERV varchar(36),
COMMENT varchar(250))

declare @xmlDoc xml
set @xmlDoc =
(
    select *  from openrowset (bulk  Файл.xml , single_blob) as a
)

insert into табличка (FNAME ,Fname_i ,OSHIB ,IN_POL ,BAS_EL ,N_ZAP ,IDCASE ,IDSERV ,COMMENT)
select t.rows.value('@FNAME','varchar(24)') as FNAME,
t.rows.value('@Fname_i','varchar(24)') as Fname_i,
t.rows.value('@OSHIB','varchar(3)') as OSHIB,
t.rows.value('@IN_POL','varchar(20)') as IN_POL,
t.rows.value('@BAS_EL','varchar(20)') as BAS_EL,
t.rows.value('@N_ZAP','varchar(4)') as N_ZAP,
t.rows.value('@IDCASE','varchar(8)') as IDCASE,
t.rows.value('@IDSERV','varchar(36)') as IDSERV,
t.rows.value('@COMMENT','varchar(250)') as COMMENT
from @xmlDoc.nodes('/FLK_P/FNAME/Fname_i/PK/') as t(rows)


Получается табличка вида
FNAME ,Fname_i ,OSHIB ,IN_POL ,BAS_EL ,N_ZAP ,IDCASE ,IDSERV ,COMMENT
Null, Null, Null, Null, Null, Null, Null, Null, Null
Null, Null, Null, Null, Null, Null, Null, Null, Null
Null, Null, Null, Null, Null, Null, Null, Null, Null

Поправте пож-та в чем не прав
30 май 14, 18:54    [16099295]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
KIRCOMS,

чета я не вижу корреляции между этим путем /FLK_P/FNAME/Fname_i/PK/ и структурой xml
30 май 14, 19:03    [16099331]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
KIRCOMS
Member

Откуда:
Сообщений: 74
как задействовать в данныx запросах xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ???
2 июн 14, 12:11    [16105232]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KIRCOMS
как задействовать в данныx запросах xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ???
А зачем его задействовывать?

Читайте букварь по XML. Про namespaces - пространства имён, префиксы.
3 июн 14, 01:30    [16109975]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
KIRCOMS
Member

Откуда:
Сообщений: 74
немножко разобрался, получилось что то вида

DECLARE @hdoc integer
DECLARE @x XML
SET @x = (select * from OpenRowset(BULK 'файл.XML' , SINGLE_BLOB) Rel(x)) 

SELECT @x

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT * FROM OPENXML (@hdoc,  'FLK_P/PR', 2) 
WITH ( 
--       [FNAME] varchar(24)    'FNAME',
--       [FNAME_I] varchar(24)  'FNAME_I',	   
       [OSHIB ]  int                   'OSHIB',
       [IM_POL]  varchar(20)      'IM_POL',
       [BAS_EL]  varchar(20)      'BAS_EL',
       [N_ZAP]   varchar(4)        'N_ZAP',
       [IDCASE]  int                  'IDCASE',
       [IDSERV]  varchar(36)      'IDSERV', 
       [COMMENT] varchar(250) 'COMMENT' 
	   )
EXEC sp_xml_removedocument @hdoc


Но имеются пара вопросов:
1. как добрать до полей элементов [FNAME], [FNAME_I] если они вышестоящие элементы

2. как разобрать структурой XML-я в одном запросе если несколько веток элементов (обход дерева элементов ?):
FLK_P2
|--FNAME
|--FNAME_I
|--ZAP_IN
ZAP_IN
|--N_ZAP
|--FAKT_STR
|--SLUCH_SUM
FAKT_STR
|--ID_PAC
|--NPOLIS
|--VPOLIS
|--SMO
|--REASON
SLUCH_SUM
|--IDCASE
|--SUM_OSN
|--SUM_SOD
|--USL_SUM
USL_SUM
|--IDSERV
|--SUM_OSN
|--SUM_SOD

Что для этого нужно допилить в заготовке

DECLARE @hdoc integer
DECLARE @x XML
SET @x = (select * from OpenRowset(BULK 'файл2.XML', SINGLE_BLOB) Rel(x)) 

SELECT @x

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT * FROM OPENXML (@hdoc,  'FLK_P2',2 ) 
WITH ( 
       [FNAME] INT              'FNAME',
       [FNAME_I] varchar(24)    'FNAME_I',	   
	   [ZAP_IN ] Varchar(255)   'ZAP_IN',
       [N_ZAP] INT              'ZAP_IN/N_ZAP',
	   [FAKT_STR] varchar(255)  'ZAP_IN/FAKT_STR',
	   [SLUCH_SUM] Varchar(255) 'ZAP_IN/SLUCH_SUM',
	   [ID_PAC] varchar(39)     'ZAP_IN/ID_PAC',
	   [NPOLIS] int           'FAKT_STR/NPOLIS',
	   [VPOLIS] int           'FAKT_STR/VPOLIS',
	   [SMO]    varchar(5)    'FAKT_STR/SMO',
	   [REASON] varchar(2)    'FAKT_STR/REASON',
	   [IDCASE] int           'SLUCH_SUM/IDCASE',
	   [SUM_OSN] int          'SLUCH_SUM/SUM_OSN',
	   [SUM_SOD] int          'SLUCH_SUM/SUM_SOD',
	   [USL_SUM] varchar(255) 'SLUCH_SUM/USL_SUM',
	   [IDSERV] varchar(36)   'USL_SUM/IDSERV',
	   [SUM_OSN1] int         'USL_SUM/SUM_OSN',
	   [SUM_SOD1] int         'USL_SUM/SUM_SOD' 
     )

EXEC sp_xml_removedocument @hdoc 
3 июн 14, 08:26    [16110214]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Вообще-то для загрузок из файлов прямиком в табличку без всяких запросов:
EXEC dbo.spXmlBulkLoad 'Z:\Path\Data.xml', 'Z:\Path\Schema.xsd'
Притом сразу во все таблицы, если их там несколько. А главное хоть гигабайты данных. Ваш запрос тут будет намного не эффективнее.

Вот инструкция: 12410359
3 июн 14, 11:34    [16111231]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
KIRCOMS
Member

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

Спасибо попробую, а если не описывать схему 'Z:\Path\Schema.xsd' и не плодить лишний файл???
3 июн 14, 12:44    [16111953]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
ВСЕ '@' убери и почитай синтаксис xpath

@Fname_i','varchar(24)') as Fname_i,
t.rows.value('@OSHIB','varchar(3)') as OSHIB,
t.rows.value('@IN_POL','varchar(20)') as IN_POL,
t.rows.value('@BAS_EL','varchar(20)') as BAS_EL,
t.rows.value('@N_ZAP','varchar(4)') as N_ZAP,
t.rows.value('@IDCASE','varchar(8)') as IDCASE,
t.rows.value('@IDSERV','varchar(36)') as IDSERV,
t.rows.value('@COMMENT'
3 июн 14, 12:52    [16112007]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Допустим у вас такая таблица:
CREATE TABLE dbo.MyTable (
	ID		Int	IDENTITY
	CONSTRAINT [PK_Company] PRIMARY KEY
,	Error		VarChar(250)
,	ImPol		VarChar(250)
,	BasEl		VarChar(250)
,	Zap		Int
,	Comment		VarChar(250)
,	Datality	Bit
)
XSD типа такой:
<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="FLK_P" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="FNAME" type="xsd:string" sql:mapped="false" />
        <xsd:element name="FNAME_I" type="xsd:string" sql:mapped="false" />
        <xsd:element maxOccurs="unbounded" name="PR" sql:relation="dbo.MyTable">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="OSHIB" type="xsd:string" sql:field="Error" />
              <xsd:element name="IM_POL" type="xsd:string" sql:field="ImPol" />
              <xsd:element name="BAS_EL" type="xsd:string" sql:field="BasEl" />
              <xsd:element name="N_ZAP" type="xsd:int" sql:field="Zap" />
              <xsd:element name="COMMENT" type="xsd:string" />
              <xsd:element name="FATALITY" type="xsd:int" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
Поменяйте имена таблицы и колонок. Может и типы данных прокорректируйте.

Делов - 3 минуты.
Но структуры XML я вашу не понял. Там что туча <P> и <FNAME> никому не нужно. Там одна строка FLK_P или множество.
Просто XML файл обязан содержать один корневой элемент согласно стандартам.
3 июн 14, 16:30    [16114050]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
KIRCOMS
Member

Откуда:
Сообщений: 74
и снова добрый день

имеется запись из файла XML
  <ZAP_IN>
    <N_ZAP>327872</N_ZAP>
    <FAKT_STR>
      <ID_PAC> 327872</ID_PAC>
      <VPOLIS>3</VPOLIS>
      <NPOLIS>111111111111111111</NPOLIS>
      <SMO>66004</SMO>
      <REASON>0</REASON>
    </FAKT_STR>
    <SLUCH_SUM>
      <IDCASE>15487043</IDCASE>
      <SUM_OSN>765</SUM_OSN>
      <SUM_SOD>42</SUM_SOD>
      <USL_SUM>
        <IDSERV>12619309</IDSERV>
        <SUM_OSN>279</SUM_OSN>
        <SUM_SOD>14</SUM_SOD>
      </USL_SUM>
      <USL_SUM>
        <IDSERV>12619634</IDSERV>
        <SUM_OSN>243</SUM_OSN>
        <SUM_SOD>14</SUM_SOD>
      </USL_SUM>
      <USL_SUM>
        <IDSERV>12619635</IDSERV>
        <SUM_OSN>243</SUM_OSN>
        <SUM_SOD>14</SUM_SOD>
      </USL_SUM>
    </SLUCH_SUM>
  </ZAP_IN>


обрабатываю запросом
DECLARE @iddoc integer             -- 
DECLARE @xmlDoc XML                -- 
Declare @P1     Varchar(255)       -- Полный Путь до файла ('\\master-server\Distrib\S660691_14041.XML')   
Declare @SSQL   Varchar(max)       -- Текс запроса
Declare @Z_FNAME varchar(24)
Declare @Z_FNAME_I Varchar(24) 
Declare @ID_PERIOD int =0


SET @xmlDoc = (select * from OpenRowset(BULK '14051.XML', SINGLE_BLOB) R(x)) 

exec sp_xml_preparedocument @iddoc OUTPUT, @xmlDoc

set @Z_FNAME=(select FNAME from  OPENXML(@iddoc, 'FLK_P2', 2)
WITH ( FNAME varchar(24)    'FNAME' ))

set @Z_FNAME_I=(select FNAME_I from  OPENXML(@iddoc, 'FLK_P2', 2)
WITH
( FNAME_I nvarchar(24) 'FNAME_I' ))


SELECT @Z_FNAME as FNAME, @Z_FNAME_I as FNAME_I,N_ZAP, ID_PAC, NPOLIS, VPOLIS , SMO , REASON , IDCASE, SUM_OSN,SUM_SOD,	IDSERV, SUM_OSN1, SUM_SOD1,@ID_PERIOD as ID_PERIOD 

 FROM OPENXML (@iddoc, 'FLK_P2/ZAP_IN',2 ) 
WITH ( 
       [N_ZAP]    INT            'N_ZAP',
	   [ID_PAC]   varchar(39)    'FAKT_STR/ID_PAC',
	   [NPOLIS]   bigint         'FAKT_STR/NPOLIS',  -- /
	   [VPOLIS]   int            'FAKT_STR/VPOLIS',  -- FAKT_STR/
	   [SMO]      varchar(5)     'FAKT_STR/SMO',     --FAKT_STR/
	   [REASON]   varchar(2)     'FAKT_STR/REASON',  -- FAKT_STR/
	   [IDCASE]   int            'SLUCH_SUM/IDCASE',
	   [SUM_OSN]  float          'SLUCH_SUM/SUM_OSN',
	   [SUM_SOD]  float          'SLUCH_SUM/SUM_SOD',	   
	   [IDSERV]   varchar(36)    'SLUCH_SUM/USL_SUM/IDSERV',
	   [SUM_OSN1] float          'SLUCH_SUM/USL_SUM/SUM_OSN',
	   [SUM_SOD1] float          'SLUCH_SUM/USL_SUM/SUM_SOD' 
     )

EXEC sp_xml_removedocument @iddoc


В итоге теряю 2 и более значений из <USL_SUM> Подскажие пож-та где не правильно (вариант с EXEC dbo.spXmlBulkLoad не предлагать)

Сообщение было отредактировано: 9 июн 14, 16:52
9 июн 14, 13:49    [16142263]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KIRCOMS
а если не описывать схему 'Z:\Path\Schema.xsd' и не плодить лишний файл???
Ну тогда пишите тонны запросов и ковыряйтесь в них. И потом не говорите что это работает в сотни раз медленее и есть тонны ресурсов.
Кстати ваш скрипт уже больше XSD, который кстати как я говорил генерируется сам. Там нужно только прописать имя таблиц(ы) и сохранить в паке рядом где будут лежать XML файлы.

KIRCOMS
В итоге теряю 2 и более значений из <USL_SUM> Подскажие пож-та где не правильно
Ошибка в 17й строке.
Особенно кода вы не ставите код в тэг SRC.
9 июн 14, 16:41    [16143948]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
SSn888
Member

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

гм... тфомсовские работники детектед :)


Вы забыли учесть, что отношение sluch...-usl... явно один-ко-многим (сами ж привели потом в [16142263] кусок, из которого видно, что втрое - внутри первого)
Так что грузите лучше последовательными командами, каждая - для отдельной ветки пути.
Для связи используйте xml-вский почти-ид.

SELECT *
 FROM OPENXML (@iddoc, 'FLK_P2',2 )  -- тут Вы указали, с какой ветки брать, остальное вне ее, как выше, так и ниже - излишне
WITH ( 
        id bigint '@mp:id', -- тот самый почти-ид
       [N_ZAP] INT 'N_ZAP') -- все остальное - не является просто элеметном этой ветки, это уже - подчиненные ей тоже ветви

SELECT *
 FROM OPENXML (@iddoc, 'FLK_P2/FAKT_STR',2 ) -- тут грузите все, что с ветки FAKT_STR
WITH ( 
            id bigint '@mp:id', -- это будет ид именно внутри этой ветки
            parentid bigint '@mp:parentid',  -- а это ссылка на ид хозяина в родительской ветке (в FLK_P2)
	   [ID_PAC]   varchar(39) ,
	   [NPOLIS]   bigint,  -- /
	   [VPOLIS]   int',  -- FAKT_STR/
	   [SMO]      varchar(5)',     --FAKT_STR/
	   [REASON]   varchar(2))
	  
     )


аналогично - с остальными ветками..
я мог где-нить опечатку допустить, особо не старался "выдать рабочий код" - уж сами доделывайте и полируйте
9 июн 14, 20:54    [16145160]     Ответить | Цитировать Сообщить модератору
 Re: Insert load XML- file to SQL  [new]
SSn888
Member

Откуда:
Сообщений: 340
небольшая правка, не все с Вашего кода лишнее стер
первую часть читать как

SELECT *
 FROM OPENXML (@iddoc, 'FLK_P2',2 )
WITH ( 
        id bigint '@mp:id', 
       [N_ZAP] INT)


там еще во втором селекте остались недотертые апострофы лишние... у типов полей... в общем - разберетесь
9 июн 14, 20:58    [16145172]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить