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

Откуда:
Сообщений: 54
Добрый день всем
Имею хмл файл

    <results>
        <unitData>
            <id>000000000000</id>
            <name>POLSKA</name>
            <values>
                <yearVal>
                    <year>1999</year>
                    <val>87</val>
                    <attrId>1</attrId>
                </yearVal>
            </values>
        </unitData>
    </results>




CREATE TABLE [DANE_TABLE](
    [id] [int] NOT NULL,
    [name] [varchar](200) NOT NULL,
    [year] [varchar](500) NOT NULL,
    [val] [varchar](500) NOT NULL,
	[attrId] [varchar] (20) NOT NULL
 CONSTRAINT [DANE_PK] PRIMARY KEY ([Id])
)
GO
INSERT INTO ATRYBUTY_TABLE ( id,name,year,val,attrId)
SELECT
   MY_XML.unitData.query('id').value('.', 'int'),
   MY_XML.unitData.query('name').value('.', 'VARCHAR(200)'),
   MY_XML.unitData.values.yearVal.query('year').value('.', 'VARCHAR(500)'),
   MY_XML.unitData.query('val').value('.', 'VARCHAR(500)'),
   MY_XML.unitData.query('attrId').value('.', 'VARCHAR(20)')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_dane.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('results/unitData') AS MY_XML (unitData);
GO
SELECT * FROM DANE_TABLE


тут ошибка
MY_XML.unitData.values.yearVal.query('year').value('.', 'VARCHAR(500)'),
и тут
nodes('results/unitData') AS MY_XML (unitData);

Msg 156, Level 15, State 1, Line 60
Неправильный синтаксис около ключевого слова "values".
Msg 156, Level 15, State 1, Line 64
Неправильный синтаксис около ключевого слова "AS".


подскажите?
26 июн 19, 11:43    [21915438]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
DECLARE @x XML = N'
<results>
    <unitData>
        <id>000000000000</id>
        <name>POLSKA</name>
        <values>
            <yearVal>
                <year>1999</year>
                <val>87</val>
                <attrId>1</attrId>
            </yearVal>
        </values>
    </unitData>
</results>'

--SELECT @x = CAST(MY_XML AS XML)
--FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_dane.xml', SINGLE_BLOB) T(MY_XML)

SELECT t.c.value('(id/text())[1]', 'int'),
       t.c.value('(name/text())[1]', 'VARCHAR(200)'),
       t.c.value('(values/yearVal/year/text())[1]', 'VARCHAR(500)'),
       t.c.value('(values/yearVal/val/text())[1]', 'VARCHAR(500)'),
       t.c.value('(values/yearVal/attrId/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/unitData') t(c)
26 июн 19, 11:58    [21915448]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
Jonsnow, ну и когда видите синенькое в редакторе - используйте квоты [values]
26 июн 19, 12:00    [21915451]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,

у меня большой этот хмл файл, нужно вчитывать его, там около дясятки тысяч
26 июн 19, 12:02    [21915452]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
ой :)
26 июн 19, 12:03    [21915454]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Jonsnow
у меня большой этот хмл файл, нужно вчитывать его, там около дясятки тысяч

Я и не предлагаю его копировать в SSMS :)
Код расскоментируйте в том примере что я прикрепил и пробуйте.
26 июн 19, 12:11    [21915458]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,


Хмммм, что-то не получается=(( ещё бы эти данные в таблицу занести вот:

SELECT * FROM DANE_TABLE

DECLARE @x XML


SELECT @x = CAST(MY_XML AS XML)
FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_dane.xml', SINGLE_BLOB) T(MY_XML)
INSERT INTO DANE_TABLE ( id,name,year,val,attrId)
SELECT t.c.value('(id/text())[1]', 'VARCHAR(200)'),
       t.c.value('(name/text())[1]', 'VARCHAR(200)'),
       t.c.value('(values/yearVal/year/text())[1]', 'VARCHAR(500)'),
       t.c.value('(values/yearVal/val/text())[1]', 'VARCHAR(500)'),
       t.c.value('(values/yearVal/attrId/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/unitData') t(c)


Это работает, спасибо
Но

SELECT t.c.value('(id/text())[1]', 'int'),

Преобразование значения "010000000000" типа nvarchar привело к переполнению столбца типа int.
Пробовал
SELECT t.c.value('(id/int)[1]', 'int'),


не помогает(
26 июн 19, 13:08    [21915500]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Всем Спасибо, всё помогло)) там тип varchar у ID=))
26 июн 19, 13:14    [21915502]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

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

Только один вопрос, можно ли как-то на констатно объявить переменую? х

Так как когда все запускаю пишет
Необходимо объявить скалярную переменную "@x".

нужно поотдельности(
26 июн 19, 13:17    [21915506]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Jonsnow
Преобразование значения "010000000000" типа nvarchar привело к переполнению столбца типа int.
Пробовал
SELECT t.c.value('(id/int)[1]', 'int')

не помогает(

Потому, что значение за +- два милиарда (с хваостиком) перевалило. У Вас там не INT (Int32), а BIGINT (Int64)
26 июн 19, 14:24    [21915594]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,

Такой вопрос имею такой тип,


DECLARE @x XML
SELECT @x = CAST(MY_XML AS XML)
FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_subjects.xml', SINGLE_BLOB) T(MY_XML)
INSERT INTO SUBJECTS_TABLE( id, [name], hasVariables, childrenID, levels)
SELECT t.[subject].value('(id/text())[1]', 'VARCHAR(50)'),
	   t.[subject].value('(name/text())[1]', 'VARCHAR(100)'),
	   t.[subject].value('(hasVariables/text())[1]', 'VARCHAR(50)'),
	   t.[subject].value('(children/id/text())[1]', 'VARCHAR(50)'),
	   t.[subject].value('(levels/id/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/subject') t([subject])

Вот это даёт один результат только, а как все занести?
за количество отвечает

SELECT t.[subject].value('(id/text())[1]', 'VARCHAR(50)'),
<results>
        <subject>
            <id>K15</id>
            <name>CENY</name>
            <hasVariables>false</hasVariables>
            <children>
                <id>G186</id>
                <id>G187</id>
                <id>G188</id>
                <id>G189</id>
                <id>G405</id>
            </children>
            <levels>
                <id>3</id>
                <id>5</id>
                <id>6</id>
                
            </levels>
        </subject>
26 июн 19, 16:00    [21915666]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Jonsnow
Вот это даёт один результат только, а как все занести?

Все это что? id которых у вас там 5 штук?
26 июн 19, 16:48    [21915693]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,
Да либо больше
26 июн 19, 16:52    [21915697]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Jonsnow
<children><id>G186</id><id>G187</id><id>G188</id><id>G189</id><id>G405</id></children>
<levels><id>3</id><id>5</id><id>6</id></levels>

В каком виде парсить? Чтобы по итогу каждое значение было отдельной строкой или чтобы все id через запятую были схлопнуты?
Если первый вариант то что делать с children/levels если парсить и то и то - это CROSS JOIN
26 июн 19, 16:59    [21915703]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,
Если Вам не сложно, то можете показать, как и так и так сделать?
Буду очень благодарен,хоть я уже благодарен очень)
26 июн 19, 20:23    [21915817]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
DECLARE @x XML = '
<results>
    <subject>
        <id>K15</id>
        <name>CENY</name>
        <hasVariables>false</hasVariables>
        <children>
            <id>G186</id>
            <id>G187</id>
            <id>G188</id>
            <id>G189</id>
            <id>G405</id>
        </children>
        <levels>
            <id>3</id>
            <id>5</id>
            <id>6</id>
        </levels>
    </subject>
</results>'

SELECT t.c.value('(id/text())[1]', 'VARCHAR(50)'),
       t.c.value('(name/text())[1]', 'VARCHAR(100)'),
       t.c.value('(hasVariables/text())[1]', 'VARCHAR(50)'),
       t2.c2.value('(text())[1]', 'VARCHAR(50)'),
       t3.c3.value('(text())[1]', 'INT'),
       t.c.value('(levels/id/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/subject') t(c)
OUTER APPLY t.c.nodes('children/id') t2(c2)
OUTER APPLY t.c.nodes('levels/id') t3(c3)
27 июн 19, 11:00    [21916006]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko,

Ещё нужна помощь)
Новый какйо-то формат хмл
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
   <s:Header>
      <a:Action s:mustUnderstand="1">http://tempuri.org/ITerytWs1/PobierzSlownikRodzajowJednostekResponse</a:Action>
      <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
         <u:Timestamp u:Id="_0">
            <u:Created>2019-07-02T08:50:56.101Z</u:Created>
            <u:Expires>2019-07-02T08:55:56.101Z</u:Expires>
         </u:Timestamp>
      </o:Security>
   </s:Header>
   <s:Body>
      <PobierzSlownikRodzajowJednostekResponse xmlns="http://tempuri.org/">
         <PobierzSlownikRodzajowJednostekResult xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <b:string>1, gmina miejska</b:string>
            <b:string>2, gmina wiejska</b:string>
            <b:string>3, gmina miejsko-wiejska</b:string>
            <b:string>4, miasto w gminie miejsko-wiejskiej</b:string>
            <b:string>5, obszar wiejski w gminie miejsko-wiejskiej</b:string>
            <b:string>8, dzielnice m. st. Warszawy</b:string>
            <b:string>9, delegatury miast: Krak&#243;w, &#321;&#243;d&#378;, Pozna&#324;, Wroc&#322;aw</b:string>
         </PobierzSlownikRodzajowJednostekResult>
      </PobierzSlownikRodzajowJednostekResponse>
   </s:Body>
</s:Envelope>


до баз данных бы как-то их тоже
2 июл 19, 11:54    [21918872]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
DECLARE @x XML = N'
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <PobierzSlownikRodzajowJednostekResponse xmlns="http://tempuri.org/">
        <PobierzSlownikRodzajowJednostekResult xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <b:string>1, gmina miejska</b:string>
        <b:string>2, gmina wiejska</b:string>
        <b:string>3, gmina miejsko-wiejska</b:string>
        <b:string>4, miasto w gminie miejsko-wiejskiej</b:string>
        <b:string>5, obszar wiejski w gminie miejsko-wiejskiej</b:string>
        <b:string>8, dzielnice m. st. Warszawy</b:string>
        <b:string>9, delegatury miast: Krak&#243;w, &#321;&#243;d&#378;, Pozna&#324;, Wroc&#322;aw</b:string>
        </PobierzSlownikRodzajowJednostekResult>
    </PobierzSlownikRodzajowJednostekResponse>
</s:Envelope>'

SELECT t.c.value('.', 'NVARCHAR(MAX)')
FROM @x.nodes('//*:string') t(c)

Смотрите или читайте за namespace:

+ старый видос

Ссылка на позицию в клипе: https://youtu.be/fu6o5Cw0XzU?t=2414
2 июл 19, 11:59    [21918874]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Sergey Syrovatchenko
DECLARE @x XML = '
<results>
    <subject>
        <id>K15</id>
        <name>CENY</name>
        <hasVariables>false</hasVariables>
        <children>
            <id>G186</id>
            <id>G187</id>
            <id>G188</id>
            <id>G189</id>
            <id>G405</id>
        </children>
        <levels>
            <id>3</id>
            <id>5</id>
            <id>6</id>
        </levels>
    </subject>
</results>'

SELECT t.c.value('(id/text())[1]', 'VARCHAR(50)'),
       t.c.value('(name/text())[1]', 'VARCHAR(100)'),
       t.c.value('(hasVariables/text())[1]', 'VARCHAR(50)'),
       t2.c2.value('(text())[1]', 'VARCHAR(50)'),
       t3.c3.value('(text())[1]', 'INT'),
       t.c.value('(levels/id/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/subject') t(c)
OUTER APPLY t.c.nodes('children/id') t2(c2)
OUTER APPLY t.c.nodes('levels/id') t3(c3)


Делаю вот так

CREATE TABLE [SUBJECTS_TABLE](
    [id] [varchar](50) NOT NULL,
	[name] [varchar] (100) NOT NULL,
	[hasVariables] [varchar] (50) NOT NULL,
	[children] [varchar] (50) NULL,
	[childrenID] [varchar] (50) NULL,
	[levels] [varchar] (20) NULL

 CONSTRAINT [SUBJECTS_PK] PRIMARY KEY ([Id])
)
GO
DECLARE @x XML
SELECT @x = CAST(MY_XML AS XML)
FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_subjects.xml', SINGLE_BLOB) T(MY_XML)
INSERT INTO SUBJECTS_TABLE( id, [name], hasVariables, children, childrenID, levels)
SELECT t.[subject].value('(id/text())[1]', 'VARCHAR(50)'),
	   t.[subject].value('(name/text())[1]', 'VARCHAR(100)'),
	   t.[subject].value('(hasVariables/text())[1]', 'VARCHAR(50)'),
	   t2.[subject2].value('(text())[1]', 'VARCHAR(50)'),
	   t3.[subject3].value('(text())[1]', 'VARCHAR(50)'),
       t.[subject].value('(levels/id/text())[1]', 'VARCHAR(20)')
FROM @x.nodes('results/subject') t([subject])
OUTER APPLY t.[subject].nodes('children/id') t2([subject2])
OUTER APPLY t.[subject].nodes('levels/id') t3([subject3])
GO

Последний раз=)
Но добавляет 0 в таблицу(
2 июл 19, 15:33    [21919115]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Konst_One
Member

Откуда:
Сообщений: 11522
DECLARE @x XML = '    <results>
        <unitData>
            <id>000000000000</id>
            <name>POLSKA</name>
            <values>
                <yearVal>
                    <year>1999</year>
                    <val>87</val>
                    <attrId>1</attrId>
                </yearVal>
                <yearVal>
                    <year>2019</year>
                    <val>1245</val>
                    <attrId>5</attrId>
                </yearVal>
            </values>
        </unitData>
    </results>';

select 
	ROW_NUMBER() OVER(ORDER BY 1/0) as num,
	v.value('(year/text())[1]', 'int') as [year],
	v.value('(val/text())[1]', 'int') as [val],
	v.value('(attrId/text())[1]', 'int') as [attrId]
FROM @x.nodes('results/unitData/values/*') t(v)
2 июл 19, 16:13    [21919174]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
FROM OPENROWSET(BULK 'E:\PRACA\API GUS\BDL API\xml_subjects.xml', SINGLE_BLOB) T(MY_XML)

Можно ли как-то 'E:\PRACA\API GUS\BDL API\xml_subjects.xml' в переменную кинуть, так как много путей, и было бы лучше что бы все было задеклорованно в переменных пробую, не получается искал решения в интернете, то нужно весь запрос изменять, и вызывать exec. Может есть какие-нибудь мысли?
Спасибо
3 июл 19, 12:06    [21919717]     Ответить | Цитировать Сообщить модератору
 Re: XML to MSSQL  [new]
Konst_One
Member

Откуда:
Сообщений: 11522
OPENROWSET(BULK @FileName...
3 июл 19, 12:10    [21919722]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить