怎么取ＸＭＬ节点的值？

Question

没法标颜色，上面xml第一行，　这２个标签请去掉，标签里面的内容在我SQL查询分析器里老有问题，是怎么回事？

Answer 1

declare @xml xml
set @xml='
<ProductCatalog>
<Products>  
<Product inventoryId="5739" name="Born Free Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack" displayProfile="standard">  <Variations>  <Variation inventoryId="BF-001" name="Born Free Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack">  <SourceImages>  <StaticSourceImage type="Both" viewName="Front">BF-001.jpg</StaticSourceImage>  </SourceImages>  </Variation>  </Variations>  </Product>  <Product inventoryId="5740" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack" displayProfile="standard">  <Variations>  <Variation inventoryId="BF-002" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack">  <SourceImages>  <StaticSourceImage type="Both" viewName="Front">BF-002.jpg</StaticSourceImage>  </SourceImages>  </Variation>  </Variations>  </Product>  <Product inventoryId="5741" name="Born Free Vented Glass Bottle - 1 pack" displayProfile="standard">  <Variations>  <Variation inventoryId="BF-003" name="Born Free Vented Glass Bottle - 1 pack">  <SourceImages>  <StaticSourceImage type="Both" viewName="Front">BF-003.jpg</StaticSourceImage>  </SourceImages>  </Variation>  </Variations>  </Product>  <Product inventoryId="5742" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz" displayProfile="standard">  <Variations>  <Variation inventoryId="BF-004" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz">  <SourceImages>  <StaticSourceImage type="Both" viewName="Front">BF-004.jpg</StaticSourceImage>  </SourceImages>  </Variation>  </Variations>  </Product>  <Product inventoryId="5743" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz" displayProfile="standard">  <Variations>  <Variation inventoryId="BF-005" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz">  <SourceImages>  <StaticSourceImage type="Both" viewName="Front">BF-005.jpg</StaticSourceImage>  </SourceImages>  </Variation>  </Variations>  </Product>  </Products> 
</ProductCatalog>
'
select t.x.value('@inventoryId','int') as inventoryId
from @xml.nodes('//Product') as t(x)5739
5740
5741
5742
5743

Answer 2

谢谢LS的高人，　但为什么xml第一行写成
<ProductCatalog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:drive="http://fluid.com/drive" xmlns="http://fluid.com/drive/catalog" xsi:schemaLocation="http://fluid.com/drive/catalog http://www.fluidretail.net/schemas/DriveProductCatalog.xsd">结果会是空的呢？源数据就有后面那一串东西，没法去掉。

Answer 3

 declare @xml  xml  
select @xml = '
<ProductCatalog  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:drive="http://fluid.com/drive" xmlns="http://fluid.com/drive/catalog" xsi:schemaLocation="http://fluid.com/drive/catalog http://www.fluidretail.net/schemas/DriveProductCatalog.xsd">
  <Products>
    <Product inventoryId="5739" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-001" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-001.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5740" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-002" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-002.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5741" name="Born Free  Vented Glass Bottle - 1 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-003" name="Born Free  Vented Glass Bottle - 1 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-003.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5742" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-004" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-004.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5743" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-005" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-005.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
  </Products>
</ProductCatalog>'
--查询结果
select li.x.value('@name','varchar(200)') as name 
from @xml.nodes('//Product') as li(x)
--结果是空的
name 如果把那两个标记去掉
--结果为
     name
Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack
Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack
Born Free  Vented Glass Bottle - 1 pack
Born Free Bisphenol-A Free Plastic Training Cup - 7 oz
Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz
分析原因 
SQL Server 2005 XML 数据类型实现了 ISO SQL-2003 标准 XML 数据类型。因此，它不仅可以存储格式良好的 XML 1.0 文档，而且可以存储所谓的 XML 内容片段（带有文本节点和任意数目的顶层元素）。在对数据进行格式良好性检查时，并不要求将 XML 数据类型绑定到 XML 架构，但是格式不规范的数据将被拒绝。 楼主的标识内容并非完全符合ISO SQL-2003标准。

Answer 4

是xmlns=惹的祸。xml name space?
我把xmlns=替换成a=, 先将就着用.

Answer 5

declare @xml  xml  
select @xml = '
<ProductCatalog  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:drive="http://fluid.com/drive" xmlns="http://fluid.com/drive/catalog" xsi:schemaLocation="http://fluid.com/drive/catalog http://www.fluidretail.net/schemas/DriveProductCatalog.xsd">
  <Products>
    <Product inventoryId="5739" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-001" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-001.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5740" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-002" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-002.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5741" name="Born Free  Vented Glass Bottle - 1 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-003" name="Born Free  Vented Glass Bottle - 1 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-003.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5742" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-004" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-004.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5743" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-005" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-005.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
  </Products>
</ProductCatalog>'
--查询结果
;
WITH XMLNAMESPACES
(
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
    'http://fluid.com/drive' AS drive,
    'http://fluid.com/drive/catalog http://www.fluidretail.net/schemas/DriveProductCatalog.xsd' AS schemaLocation,
    DEFAULT 'http://fluid.com/drive/catalog'
)
SELECT 
   li.x.value('@name','varchar(200)') AS name 
FROM
   @xml.nodes('//Product') as li(x)

Answer 6

--用xmlns，就得加dufault,其他的直接查就行了
;WITH XMLNAMESPACES(DEFAULT 'http://fluid.com/drive/catalog')
select t.x.value('@inventoryId','int') as inventoryId
from @xml.nodes('//Product') as t(x)

Answer 7

declare @xml xml
set @xml='<ProductCatalog>
  <Products>
    <Product inventoryId="5739" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-001" name="Born Free  Bisphenol-A Free Plastic Bottle - 9 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-001.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5740" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-002" name="Born Free Bisphenol-A Free Plastic Bottle - 5 oz - 2 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-002.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5741" name="Born Free  Vented Glass Bottle - 1 pack" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-003" name="Born Free  Vented Glass Bottle - 1 pack">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-003.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5742" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-004" name="Born Free Bisphenol-A Free Plastic Training Cup - 7 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-004.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
    <Product inventoryId="5743" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz" displayProfile="standard">
      <Variations>
        <Variation inventoryId="BF-005" name="Born Free Bisphenol-A Free Plastic Drinking Cup - 9 oz">
          <SourceImages>
            <StaticSourceImage type="Both" viewName="Front">BF-005.jpg</StaticSourceImage>
          </SourceImages>
        </Variation>
      </Variations>
    </Product>
  </Products>
</ProductCatalog>'
select d.c.value('@inventoryId' ,'int')   from @xml.nodes('ProductCatalog/Products/Product')  as d(c)
-----------
5739
5740
5741
5742
5743