loading..
Русский    English
19:27
листать

Методы типа данных XML стр. 1

Чебыкин Д.Н.

Тип данных XML впервые появился в  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2005.

Он может содержать до 2 Гб данных.

В языке  Transact-SQL (T-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.Transact-SQL имеется пять методов для работы с типом данных XML:

  • query() – используется для извлечения XML фрагментов из XML документов;
  • value() – используется для извлечения значений конкретных узлов или атрибутов XML документов;
  • exist() – используется для проверки существования узла или атрибута. Возвращает 1, если узел или атрибут найден, и 0, если не найден;
  • modify() – изменяет XML документ;
  • nodes() – разделяет XML документ на несколько строк по узлам.

Методы типа данных XML принимают на вход выражение XPath  или запрос XQuery.

Для примеров в данном учебнике будем использовать базу данных с таблицами, содержащими столбцы типа данных XML.

Таблица tArtist содержит информацию о музыкальных группах, исполнителях и их альбомах.

  1. CREATE TABLE dbo.tArtist (
  2.       artistId INT NOT NULL PRIMARY KEY
  3.     , name VARCHAR(100) NOT NULL
  4.     , xmlData XML NOT NULL
  5. );

Заполним таблицы тестовыми данными.

  1. INSERT INTO dbo.tArtist (artistId, name, xmlData) VALUES
  2. (1, 'Radiohead',
  3. '<albums>
  4.     <album title="The King of Limbs">
  5.         <labels>
  6.             <label>Self-released</label>
  7.         </labels>
  8.         <song title="Bloom" length="5:15"/>
  9.         <song title="Morning Mr Magpie" length="4:41"/>
  10.         <song title="Little by Little" length="4:27"/>
  11.         <song title="Feral" length="3:13"/>
  12.         <song title="Lotus Flower" length="5:01"/>
  13.         <song title="Codex" length="4:47"/>
  14.         <song title="Give Up the Ghost" length="4:50"/>
  15.         <song title="Separator" length="5:20"/>
  16.         <description link="http://en.wikipedia.org/wiki/The_King_of_Limbs">
  17.         The King of Limbs is the eighth studio album by English rock band Radiohead,
  18.         produced by Nigel Godrich. It was self-released on 18 February 2011 as a
  19.         download in MP3 and WAV formats, followed by physical CD and 12" vinyl
  20.         releases on 28 March, a wider digital release via AWAL, and a special
  21.         "newspaper" edition on 9 May 2011. The physical editions were released
  22.         through the band''s Ticker Tape imprint on XL in the United Kingdom,
  23.         TBD in the United States, and Hostess Entertainment in Japan.
  24.         </description>
  25.     </album>
  26.     <album title="OK Computer">
  27.         <labels>
  28.             <label>Parlophone</label>
  29.             <label>Capitol</label>
  30.         </labels>
  31.         <song title="Airbag" length="4:44"/>
  32.         <song title="Paranoid Android" length="6:23"/>
  33.         <song title="Subterranean Homesick Alien" length="4:27"/>
  34.         <song title="Exit Music (For a Film)" length="4:24"/>
  35.         <song title="Let Down" length="4:59"/>
  36.         <song title="Karma Police" length="4:21"/>
  37.         <song title="Fitter Happier" length="1:57"/>
  38.         <song title="Electioneering" length="3:50"/>
  39.         <song title="Climbing Up the Walls" length="4:45"/>
  40.         <song title="No Surprises" length="3:48"/>
  41.         <song title="Lucky" length="4:19"/>
  42.         <song title="The Tourist" length="5:24"/>
  43.         <description link="http://en.wikipedia.org/wiki/OK_Computer">
  44.         OK Computer is the third studio album by the English alternative rock band
  45.         Radiohead, released on 16 June 1997 on Parlophone in the United Kingdom and
  46.         1 July 1997 by Capitol Records in the United States. It marks a deliberate
  47.         attempt by the band to move away from the introspective guitar-oriented
  48.         sound of their previous album The Bends. Its layered sound and wide range
  49.         of influences set it apart from many of the Britpop and alternative rock
  50.         bands popular at the time and laid the groundwork for Radiohead''s later,
  51.         more experimental work.
  52.         </description>
  53.     </album>
  54. </albums>'),
  55. (2, 'Guns N'' Roses',
  56. '<albums>
  57.     <album title="Use Your Illusion I">
  58.         <labels>
  59.             <label>Geffen Records</label>
  60.         </labels>
  61.         <song title="Right Next Door to Hell" length="3:02"/>
  62.         <song title="Dust N'' Bones" length="4:58"/>
  63.         <song title="Live and Let Die (Paul McCartney and Wings cover)"
  64.         length="3:04"/>
  65.         <song title="Don''t Cry (original version)" length="4:44"/>
  66.         <song title="Perfect Crime" length="2:24"/>
  67.         <song title="You Ain''t the First" length="2:36"/>
  68.         <song title="Bad Obsession" length="5:28"/>
  69.         <song title="Back Off Bitch" length="5:04"/>
  70.         <song title="Double Talkin'' Jive" length="3:24"/>
  71.         <song title="November Rain" length="8:57"/>
  72.         <song title="The Garden (featuring Alice Cooper and Shannon Hoon)"
  73.         length="5:22"/>
  74.         <song title="Garden of Eden" length="2:42"/>
  75.         <song title="Don''t Damn Me" length="5:19"/>
  76.         <song title="Bad Apples" length="4:28"/>
  77.         <song title="Dead Horse" length="4:18"/>
  78.         <song title="Coma" length="10:13"/>
  79.         <description link="http://ru.wikipedia.org/wiki/Use_Your_Illusion_I">
  80.         Use Your Illusion I is the third studio album by GnR. It was the first of two
  81.         albums released in conjunction with the Use Your Illusion Tour, the other
  82.         being Use Your Illusion II. The two are thus sometimes considered a double album.
  83.         In fact, in the original vinyl releases, both Use Your Illusion albums are
  84.         double albums. Material for all two/four discs (depending on the medium) was
  85.         recorded at the same time and there was some discussion of releasing a
  86.         ''quadruple album''. The album debuted at No. 2 on the Billboard charts, selling
  87.         685,000 copies in its first week, behind Use Your Illusion II''s first week sales
  88.         of 770,000. Use Your Illusion I has sold 5,502,000 units in the U.S. as of 2010,
  89.         according to Nielsen SoundScan. It was nominated for a Grammy Award in 1992.
  90.         </description>
  91.     </album>
  92.     <album title="Use Your Illusion II">
  93.         <labels>
  94.             <label>Geffen Records</label>
  95.         </labels>
  96.         <song title="Civil War" length="7:42"/>
  97.         <song title="14 Years" length="4:21"/>
  98.         <song title="Yesterdays" length="3:16"/>
  99.         <song title="Knockin'' on Heaven''s Door (Bob Dylan cover)" length="5:36"/>
  100.         <song title="Get in the Ring" length="5:41"/>
  101.         <song title="Shotgun Blues" length="3:23"/>
  102.         <song title="Breakdown" length="7:05"/>
  103.         <song title="Pretty Tied Up" length="4:48"/>
  104.         <song title="Locomotive (Complicity)" length="8:42"/>
  105.         <song title="So Fine" length="4:06"/>
  106.         <song title="Estranged" length="9:24"/>
  107.         <song title="You Could Be Mine" length="5:43"/>
  108.         <song title="Don''t Cry (Alternate lyrics)" length="4:44"/>
  109.         <song title="My World" length="1:24"/>
  110.         <description link="http://ru.wikipedia.org/wiki/Use_Your_Illusion_II">
  111.         Use Your Illusion II is the fourth studio album by GnR. It was one of two albums
  112.         released in conjunction with the Use Your Illusion Tour, and as a result the two
  113.         albums are sometimes considered a double album. Bolstered by lead single ''You
  114.         Could Be Mine'', Use Your Illusion II was the slightly more popular of the two
  115.         albums, selling 770,000 copies its first week and debuting at No. 1 on the U.S.
  116.         charts, ahead of Use Your Illusion I''s first week sales of 685,000.
  117.         </description>
  118.     </album>
  119. </albums>');


Bookmark and Share
Страницы: 1 2 3 4 5 6
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
вчера
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100