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

转载于:https://www.cnblogs.com/zhangpan1244/archive/2012/09/04/2669884.html