SQL2008中的XML字段操作，与命名空间相关

 1 -- ================================================
 2 -- Proc Function : 测试2008中XML字段
 3 -- Create Date   : 2012-09-04
 4 -- Create User   : IsaacZhang
 5 -- Update User   : IsaacZhang
 6 -- ================================================
 7 select Authors
 8 declare @data xml;
 9 set @data = '<?xml version="1.0"?>
10 <ArrayOfAuthor xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
11   <Author>
12     <AuthorId xmlns="http://services.notefirst.com/Type/Author">6d3723e8-dbdc-4e40-bc40-e558d4631e1b</AuthorId>
13     <FirstName xmlns="http://services.notefirst.com/Type/Author">Alice</FirstName>
14     <LastName xmlns="http://services.notefirst.com/Type/Author">Baptista</LastName>
15     <MiddleName xmlns="http://services.notefirst.com/Type/Author">Ana</MiddleName>
16     <FullName xmlns="http://services.notefirst.com/Type/Author">Ana Alice Baptista</FullName>
17     <Email xmlns="http://services.notefirst.com/Type/Author" />
18     <Organization xmlns="http://services.notefirst.com/Type/Author" />
19     <BibliographyId xmlns="http://services.notefirst.com/Type/Author">343032de-9eb8-4a54-9529-02c7be601d1d</BibliographyId>
20     <IsOrganization xmlns="http://services.notefirst.com/Type/Author">false</IsOrganization>
21     <Order xmlns="http://services.notefirst.com/Type/Author">0</Order>
22     <Spell_LastName xmlns="http://services.notefirst.com/Type/Author" />
23     <Spell_FirstName xmlns="http://services.notefirst.com/Type/Author" />
24     <Spell_MiddleName xmlns="http://services.notefirst.com/Type/Author" />
25     <IsSpell xmlns="http://services.notefirst.com/Type/Author">false</IsSpell>
26     <FullNameEn xmlns="http://services.notefirst.com/Type/Author">Ana Alice Baptista</FullNameEn>
27   </Author>
28   <Author>
29     <AuthorId xmlns="http://services.notefirst.com/Type/Author">af4642cb-bf85-40d6-b613-f44b9932e527</AuthorId>
30     <FirstName xmlns="http://services.notefirst.com/Type/Author">Barbosa</FirstName>
31     <LastName xmlns="http://services.notefirst.com/Type/Author">Machado</LastName>
32     <MiddleName xmlns="http://services.notefirst.com/Type/Author">Altamiro</MiddleName>
33     <FullName xmlns="http://services.notefirst.com/Type/Author">Altamiro Barbosa Machado</FullName>
34     <Email xmlns="http://services.notefirst.com/Type/Author" />
35     <Organization xmlns="http://services.notefirst.com/Type/Author" />
36     <BibliographyId xmlns="http://services.notefirst.com/Type/Author">343032de-9eb8-4a54-9529-02c7be601d1d</BibliographyId>
37     <IsOrganization xmlns="http://services.notefirst.com/Type/Author">false</IsOrganization>
38     <Order xmlns="http://services.notefirst.com/Type/Author">0</Order>
39     <Spell_LastName xmlns="http://services.notefirst.com/Type/Author" />
40     <Spell_FirstName xmlns="http://services.notefirst.com/Type/Author" />
41     <Spell_MiddleName xmlns="http://services.notefirst.com/Type/Author" />
42     <IsSpell xmlns="http://services.notefirst.com/Type/Author">false</IsSpell>
43     <FullNameEn xmlns="http://services.notefirst.com/Type/Author">Altamiro Barbosa Machado</FullNameEn>
44   </Author>
45 </ArrayOfAuthor>';
46   
47 
48    --WITH XMLNAMESPACES ( N'' as a)
49 
50 /*select @data.query('declare default element namespace "http://services.notefirst.com/Type/Author";
51  (//FullName)');
52  select @data.value('(//FullName)[1]','varchar(200)');
53 select @data.query('Author');*/
54 declare @isHas varchar(3);
55 select  @isHas = @data.exist('declare default element namespace "http://services.notefirst.com/Type/Author";//FullName');
56 select Cast(@isHas as int)+1
57 --select @data.exist('//author');
58 
59 --select @data.nodes('//Author/FullName');
60 
61 select T.C.value('declare default element namespace "http://services.notefirst.com/Type/Author";(../FullName)[1]','varchar(200)') 
62 as authors
63 from @data.nodes('declare default element namespace "http://services.notefirst.com/Type/Author";(//FullName)') as T(C)
64 
65 --select top 10 * from dbo.Bibliography