XML Data Type Methods page 1

Chebykin D.N.

The XML data type was first introduced with  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005.

This data type allows you to store XML data up to 2 GB.

There are five XML data type methods:

  • query() Used to extract XML fragments from an XML data type.
  • value() Used to extract a single value from an XML document.
  • exist() Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
  • modify() Updates XML data in an XML data type.
  • nodes() Shreds XML data into multiple rows by nodes.

XML data type methods require an XPath expression or XQuery query.

As an example we'll use database with XML data type fields.

Table tArtist contains information about music bands, singers and their albums.

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

Let's fill tables with sample data.

  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
Pages 1 2 3 4 5 6
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.