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

Откуда:
Сообщений: 27
Добрый день, нужна ваша помощь,
есть XML код:
<?xml version="1.0" encoding="UTF-8"?>
<string xmlns="http://www.webserviceX.NET"><?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>
<Location>Berlin-Tegel, Germany (EDDT) 52-34N 013-19E 37M</Location>
<Time>Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC</Time>
<Wind> from the NE (050 degrees) at 2 MPH (2 KT):0</Wind>
<Visibility> 3 mile(s):0</Visibility>
<Temperature> 42 F (6 C)</Temperature>
<DewPoint> 39 F (4 C)</DewPoint>
<RelativeHumidity> 86%</RelativeHumidity>
<Pressure> 30.18 in. Hg (1022 hPa)</Pressure>
<Status>Success</Status>
</CurrentWeather>
</string>

его нужно записать в базу данных. Мой код:

CREATE TABLE CurrentWeather(
Location Varchar(50)PRIMARY KEY,
[Time] Date,
Wind varchar (250),
Visibility varchar (250),
Temperature varchar (250),
DewPoint varchar (250),
RelativeHumidity varchar (250),
Pressure varchar (250),
[Status] varchar (250));


INSERT INTO CurrentWeather
(Location, [Time], Wind, Visibility, Temperature,DewPoint, RelativeHumidity,
Pressure, [Status]

SELECT X.Location.query('Location').value('Varchar'),
X.Location.query('Wind').value('varchar'),
X.Location.query('Visibility').value('varchar'),
X.Location.query('Temperature').value('varchar'),
X.Location.query('DewPoint').value('varchar'),
X.Location.query('RelativeHumidity').value('varchar'),
X.Location.query('Pressure').value('varchar'),
X.Location.query('[Status]').value('varchar'),

FROM (

SELECT CAST(x AS XML)

FROM OPENROWSET(

BULK 'C:\Airport\Weather.xml',

SINGLE_BLOB) AS T(x)

) AS T(x)

CROSS APPLY x.nodes('CurrentWeather/Location') AS X(Location);

он не работает.... помогите пожста
30 ноя 11, 16:14    [11684605]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
aleks2
Guest
declare @xml as XML
set @xml='
<?xml version="1.0" encoding="UTF-8"?>
<string xmlns="http://www.webserviceX.NET"><?xml version="1.0" encoding="utf-16"?> 
<CurrentWeather> 
<Location>Berlin-Tegel, Germany (EDDT) 52-34N 013-19E 37M</Location> 
<Time>Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC</Time> 
<Wind> from the NE (050 degrees) at 2 MPH (2 KT):0</Wind> 
<Visibility> 3 mile(s):0</Visibility> 
<Temperature> 42 F (6 C)</Temperature> 
<DewPoint> 39 F (4 C)</DewPoint> 
<RelativeHumidity> 86%</RelativeHumidity> 
<Pressure> 30.18 in. Hg (1022 hPa)</Pressure> 
<Status>Success</Status> 
</CurrentWeather>
</string>
'

Msg 9438, Level 16, State 1, Line 3
Синтаксический анализ XML: строка 2, символ 6, text/xmldecl расположено не в начале входных данных

Кагбе намекает...
30 ноя 11, 17:06    [11685032]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
Видоизменив
Guest
Можно быстро решить, чуть видоизменив xml

declare @x as xml =
'<string>
<CurrentWeather>
<Location>Berlin-Tegel, Germany (EDDT) 52-34N 013-19E 37M</Location>
<Time>Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC</Time>
<Wind> from the NE (050 degrees) at 2 MPH (2 KT):0</Wind>
<Visibility> 3 mile(s):0</Visibility>
<Temperature> 42 F (6 C)</Temperature>
<DewPoint> 39 F (4 C)</DewPoint>
<RelativeHumidity> 86%</RelativeHumidity>
<Pressure> 30.18 in. Hg (1022 hPa)</Pressure>
<Status>Success</Status>
</CurrentWeather>
</string>'


declare @CurrentWeather as table (
Location Varchar(50)PRIMARY KEY,
[Time] Date,
Wind varchar (250),
Visibility varchar (250),
Temperature varchar (250),
DewPoint varchar (250),
RelativeHumidity varchar (250),
Pressure varchar (250),
[Status] varchar (250));



select r.value('Location[1]','varchar(50)')
	,(r.value('Time[1]','varchar(200)'))
	,r.value('Wind[1]','varchar(250)')
	,r.value('Visibility[1]','varchar(250)')
	,r.value('Temperature[1]','varchar(250)')
	,r.value('DewPoint[1]','varchar(250)')
	,r.value('RelativeHumidity[1]','varchar(250)')
	,r.value('Pressure[1]','varchar(250)')
	,r.value('Status[1]','varchar(250)')
from (select x= @x) t
cross apply t.x.nodes('string/CurrentWeather') z (r)

Есть здесь спец с Кишенева по xml чуть что лучше к нему если такой вариант не устроит
30 ноя 11, 17:40    [11685328]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
В догонку..
Guest
Видоизменив,

да... и проблемы с датой сразу вижу
Нужно будет конвертировать специальным образом
30 ноя 11, 17:41    [11685339]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27
В догонку..,

Спасибо за ответы!!!!
а как изменить дату?
30 ноя 11, 18:02    [11685494]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27
Еше вопрос а как записать данные в таблицу БД?
30 ноя 11, 18:18    [11685551]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27

Есть здесь спец с Кишенева по xml чуть что лучше к нему если такой вариант не устроит

А где его найти?
30 ноя 11, 18:20    [11685558]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27
nuhrab
Еше вопрос а как записать данные в таблицу БД?

Мой код:
INSERT INTO CurrentWeather 
(Location, [Time], Wind, Visibility, Temperature, DewPoint, RelativeHumidity, Pressure, [Status])

    SELECT  X.string.query('Location').value('.','VARCHAR(300)'),
		    X.string.query('Time').value('.','VARCHAR(300)'),
            X.string.query('Wind').value('.','VARCHAR(300)'),
            X.string.query('Visibility').value('.','VARCHAR(300)'),
            X.string.query('Temperature').value('.','VARCHAR(300)'),
            X.string.query('DewPoint').value('.','VARCHAR(300)'),
            X.string.query('RelativeHumidity').value('.','VARCHAR(300)'),
            X.string.query('Pressure').value('.','VARCHAR(300)'),
            X.string.query('Status').value('.','VARCHAR(300)')

    FROM (

    SELECT CAST(x AS XML)

    FROM OPENROWSET(

        BULK 'C:\Airport\Weather.xml',

        SINGLE_BLOB) AS T(x)

        ) AS T(x)

CROSS APPLY x.nodes('String/CurrentWeather') AS X(CurrentWeather);
не работает :(
30 ноя 11, 18:27    [11685579]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
НУ так предупреждали же...
Guest
nuhrab
nuhrab
Еше вопрос а как записать данные в таблицу БД?

Мой код:
INSERT INTO CurrentWeather 
(Location, [Time], Wind, Visibility, Temperature, DewPoint, RelativeHumidity, Pressure, [Status])

    SELECT  X.string.query('Location').value('.','VARCHAR(300)'),
		    X.string.query('Time').value('.','VARCHAR(300)'),
            X.string.query('Wind').value('.','VARCHAR(300)'),
            X.string.query('Visibility').value('.','VARCHAR(300)'),
            X.string.query('Temperature').value('.','VARCHAR(300)'),
            X.string.query('DewPoint').value('.','VARCHAR(300)'),
            X.string.query('RelativeHumidity').value('.','VARCHAR(300)'),
            X.string.query('Pressure').value('.','VARCHAR(300)'),
            X.string.query('Status').value('.','VARCHAR(300)')

    FROM (

    SELECT CAST(x AS XML)

    FROM OPENROWSET(

        BULK 'C:\Airport\Weather.xml',

        SINGLE_BLOB) AS T(x)

        ) AS T(x)

CROSS APPLY x.nodes('String/CurrentWeather') AS X(CurrentWeather);
не работает :(


Не работает, потому как X.string.query('Time').value('.','VARCHAR(300)') - возвращает строку... а там должна быть дата...

по идее у вас на эту xml должна быть схема... по крайней мере в первоначальном варианте на нее ссылка есть:
xmlns="http://www.webserviceX.NET" по правильному нужно ее добавить с сервер. Но мне кажется проще Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC вам будет перевести в дату, чем объяснять с нуля про схему
30 ноя 11, 18:40    [11685626]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27
НУ так предупреждали же...
nuhrab
пропущено...

Мой код:
INSERT INTO CurrentWeather 
(Location, [Time], Wind, Visibility, Temperature, DewPoint, RelativeHumidity, Pressure, [Status])

    SELECT  X.string.query('Location').value('.','VARCHAR(300)'),
		    X.string.query('Time').value('.','VARCHAR(300)'),
            X.string.query('Wind').value('.','VARCHAR(300)'),
            X.string.query('Visibility').value('.','VARCHAR(300)'),
            X.string.query('Temperature').value('.','VARCHAR(300)'),
            X.string.query('DewPoint').value('.','VARCHAR(300)'),
            X.string.query('RelativeHumidity').value('.','VARCHAR(300)'),
            X.string.query('Pressure').value('.','VARCHAR(300)'),
            X.string.query('Status').value('.','VARCHAR(300)')

    FROM (

    SELECT CAST(x AS XML)

    FROM OPENROWSET(

        BULK 'C:\Airport\Weather.xml',

        SINGLE_BLOB) AS T(x)

        ) AS T(x)

CROSS APPLY x.nodes('String/CurrentWeather') AS X(CurrentWeather);
не работает :(


Не работает, потому как X.string.query('Time').value('.','VARCHAR(300)') - возвращает строку... а там должна быть дата...

по идее у вас на эту xml должна быть схема... по крайней мере в первоначальном варианте на нее ссылка есть:
xmlns="http://www.webserviceX.NET" по правильному нужно ее добавить с сервер. Но мне кажется проще Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC вам будет перевести в дату, чем объяснять с нуля про схему



Спасибо за ответ. вопрос а как конвертировать :(
(convert (r.value('Time[1]','datetime'))
30 ноя 11, 19:02    [11685743]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
nuhrab
а как конвертировать
SET LANGUAGE English
SELECT	 Replace(Replace(Left([Time],PatIndex('%/%',[Time])-6),',',''),'-','')
	,Convert(DateTime,Replace(Replace(Left([Time],PatIndex('%/%',[Time])-6),',',''),'-',''),100)
	,Stuff(Stuff(Right([Time],19),16,4,''),14,0,':')
	,Convert(DateTime,Stuff(Stuff(Right([Time],19),16,4,''),14,0,':'),104)
FROM	dbo.CurrentWeather
StrDateDateStrUTCUTC
Nov 08 2011 05:20 PM2011-11-08 17:20:00.0002011.11.08 22:202011-08-11 22:20:00.000
30 ноя 11, 20:04    [11685973]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

Откуда:
Сообщений: 27
Огромное спасибо за помощь! у меня выдается ошибка Could not find stored procedure 'BankAdmin.dbo.spSaveToFile' .

Сообщение было отредактировано: 30 ноя 11, 22:31
30 ноя 11, 21:23    [11686254]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Подчистил топик по просьбе Mnior.
30 ноя 11, 22:33    [11686564]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Гавриленко Сергей Алексеевич, спасибо.

nuhrab, 9759065
USE tempdb
GO
CREATE TABLE [dbo].[CurrentWeather] (
	 Location		VarChar(250)
	,[Time]			VarChar(50)
	,Wind			VarChar(50)
	,Visibility		VarChar(50)
	,Temperature		VarChar(50)
	,DewPoint		VarChar(50)
	,RelativeHumidity	VarChar(50)
	,Pressure		VarChar(50)
	,[Status]		VarChar(50)
)
GO
-- Тестовые файл и файл схемы
DECLARE	 @File		SysName = 'Z:\Path\Test.xml'
	,@Schema	SysName = 'Z:\Path\Test.xsd'
EXEC dbo.spSaveToFile @File,'<?xml version="1.0"?>
<string>
 <CurrentWeather>
  <Location>Berlin-Tegel, Germany (EDDT) 52-34N 013-19E 37M</Location>
  <Time>Nov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC</Time>
  <Wind> from the NE (050 degrees) at 2 MPH (2 KT):0</Wind>
  <Visibility> 3 mile(s):0</Visibility>
  <Temperature> 42 F (6 C)</Temperature>
  <DewPoint> 39 F (4 C)</DewPoint>
  <RelativeHumidity> 86%</RelativeHumidity>
  <Pressure> 30.18 in. Hg (1022 hPa)</Pressure>
  <Status>Success</Status>
 </CurrentWeather>
</string>'
EXEC dbo.spSaveToFile @Schema,'<?xml version="1.0"?>
<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="string" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CurrentWeather" minOccurs="0" maxOccurs="unbounded" sql:relation="dbo.CurrentWeather">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Location" type="xsd:string" />
              <xsd:element name="Time" type="xsd:string" />
              <xsd:element name="Wind" type="xsd:string" />
              <xsd:element name="Visibility" type="xsd:string" />
              <xsd:element name="Temperature" type="xsd:string" />
              <xsd:element name="DewPoint" type="xsd:string" />
              <xsd:element name="RelativeHumidity" type="xsd:string" />
              <xsd:element name="Pressure" type="xsd:string" />
              <xsd:element name="Status" type="xsd:string" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>'

-- Закачка из файл в таблицу
EXEC dbo.spXMLBulkLoad @File,@Schema,'tempdb'

SET LANGUAGE English
SELECT	 Replace(Replace(Left([Time],PatIndex('%/%',[Time])-6),',',''),'-','')
	,Convert(DateTime,Replace(Replace(Left([Time],PatIndex('%/%',[Time])-6),',',''),'-',''),100)
	,Stuff(Stuff(Right([Time],19),16,4,''),14,0,':')
	,Convert(DateTime,Stuff(Stuff(Right([Time],19),16,4,''),14,0,':'),104)
	,*
FROM	dbo.CurrentWeather
GO
DROP TABLE [dbo].[CurrentWeather]
StrDateDateStrUTCUTCLocationTimeWindVisibilityTemperatureDewPointRelativeHumidityPressureStatus
Nov 08 2011 05:20 PM2011-11-08 17:20:00.0002011.11.08 22:202011-08-11 22:20:00.000Berlin-Tegel, Germany (EDDT) 52-34N 013-19E 37MNov 08, 2011 - 05:20 PM EST / 2011.11.08 2220 UTC from the NE (050 degrees) at 2 MPH (2 KT):0 3 mile(s):0 42 F (6 C) 39 F (4 C) 86% 30.18 in. Hg (1022 hPa)Success
[/quot]
1 дек 11, 10:34    [11687731]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Мда, всё таки не успел.
nuhrab
Огромное спасибо за помощь! у меня выдается ошибка Could not find stored procedure dbo.spSaveToFile.
9803638
Но это тебе не нужно, просто показал что загрузка идёт из файла. Это просто работающий пример.
1 дек 11, 10:39    [11687762]     Ответить | Цитировать Сообщить модератору
 Re: Import XML в Базу Данных  [new]
nuhrab
Member

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

спасибо большое!
1 дек 11, 11:53    [11688290]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить