问无法使用SchemaId执行ReadXML操作
EN

Stack Overflow用户

提问于 2016-07-26 15:00:39

回答 1查看 14关注 0票数 1

Declare @XML XML
SET @XML =
'<?xml version="1.0" encoding="UTF-8"?>
<Dealer schemaID="DL" xmlns="http://www.abc.zy/kudos/kentucy" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.abc.zy/kudos/kentucy/ik schema13_dealer(dl).xsd">
   <Head>
      <ImporterNr>ZZ840</ImporterNr>
      <ForwarderNr>XYZA</ForwarderNr>
      <RecordType>MR</RecordType>
      <Filename>FZ12325</Filename>
      <Items>
         <Item>
            <AddressType>L</AddressType>
            <DealerNr>KZ DEALER LLC</DealerNr>
            <ShortName>KZ D </ShortName>
            <DealerName>DEALER T </DealerName>
            <Street>Riverpol Romerro Calle 31D No. 12-25</Street>
            <Country>SG </Country>
            <ZIP>1234</ZIP>
            <Town>MCGY</Town>
         </Item>
      </Items>
   </Head>
   </Dealer>'

我的查询是这样的

SELECT
   Cust.value('(ImporterNr)[1]', 'nvarchar(50)') AS 'ImporterNr',
   Cust.value('(ForwarderNr)[1]', 'nVarchar(50)') AS 'ForwarderNr',
    Cust.value('(Items/Item/AddressType)[1]', 'nVarchar(50)') AS 'AddressType'
FROM
   @XML.nodes('/Dealer/Head') AS AOC(Cust)

3列ImporterNr，ForwarderNr，AddressType返回空值，如果我做错了什么请给我建议。

声明@XML XML SET @XML =

'<?xml version="1.0" encoding="UTF-8"?>
<Dealer schemaID="DL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.abc.zy/kudos/kentucy/ik schema13_dealer(dl).xsd">
   <Head>
      <ImporterNr>ZZ840</ImporterNr>
      <ForwarderNr>XYZA</ForwarderNr>
      <RecordType>MR</RecordType>
      <Filename>FZ12325</Filename>
      <Items>
         <Item>
            <AddressType>L</AddressType>
            <DealerNr>KZ DEALER LLC</DealerNr>
            <ShortName>KZ D </ShortName>
            <DealerName>DEALER T </DealerName>
            <Street>Riverpol Romerro Calle 31D No. 12-25</Street>
            <Country>SG </Country>
            <ZIP>1234</ZIP>
            <Town>MCGY</Town>
         </Item>
         <Item>
            <AddressType>L</AddressType>
            <DealerNr>ALIAV</DealerNr>
            <ShortName>K V </ShortName>
            <DealerName>K T VALLEDUPAR                              </DealerName>
            <Street>CARRERA 19 KM 0-700 CLUB DEPORTIVO RUDATO         </Street>
            <Country>ZO </Country>
            <ZIP>123       </ZIP>
            <Town>VALLEDUPAR                                        </Town>
         </Item>
      </Items>
   </Head>
   </Dealer>'

如果存在多个节点，查询将不起作用。

sql-server

回答 1

Stack Overflow用户

发布于 2016-07-26 15:04:25

将命名空间添加到查询中：

Declare @XML XML
SET @XML =
'<?xml version="1.0" encoding="UTF-8"?>
<Dealer schemaID="DL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.abc.zy/kudos/kentucy/ik schema13_dealer(dl).xsd">
   <Head>
      <ImporterNr>ZZ840</ImporterNr>
      <ForwarderNr>XYZA</ForwarderNr>
      <RecordType>MR</RecordType>
      <Filename>FZ12325</Filename>
      <Items>
         <Item>
            <AddressType>L</AddressType>
            <DealerNr>KZ DEALER LLC</DealerNr>
            <ShortName>KZ D </ShortName>
            <DealerName>DEALER T </DealerName>
            <Street>Riverpol Romerro Calle 31D No. 12-25</Street>
            <Country>SG </Country>
            <ZIP>1234</ZIP>
            <Town>MCGY</Town>
         </Item>
      </Items>
   </Head>
   </Dealer>'

;WITH XMLNAMESPACES ('http://www.abc.zy/kudos/kentucy' as ns1)
SELECT
   Cust.value('(ns1:ImporterNr)[1]', 'nvarchar(50)') AS 'ImporterNr',
   Cust.value('(ns1:ForwarderNr)[1]', 'nVarchar(50)') AS 'ForwarderNr',
   Cust.value('(ns1:Items/ns1:Item/ns1:AddressType)[1]', 'nVarchar(50)') AS 'AddressType'
FROM
   @XML.nodes('/ns1:Dealer/ns1:Head') AS AOC(Cust)

或者将其从XML中删除。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/38583066

复制

相似问题

问无法使用SchemaId执行ReadXML操作
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法使用SchemaId执行ReadXML操作EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法使用SchemaId执行ReadXML操作
EN