sql-server - SQL Server : select from xml with namespace

Question

我有如下代码:

declare @strXML nvarchar(max) =
'<?xml version="1.0" encoding="utf-16"?>
    <GetDictResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <GetDictResult>
    <Success xmlns="http://or.org/ValidationSchema.xsd">true</Success>
    <Dicts xmlns="http://or.org/ValidationSchema.xsd">
      <Dict>
        <Code>TADR</Code>
        <Name>Type of address</Name>
      </Dict>
    </Dicts>
  </GetDictResult>
</GetDictResponse>
'

declare @xml xml =  CONVERT(XML,CONVERT(NVARCHAR(MAX),@strXML))

--empty row result
SELECT
    ltrim(rtrim(T.c.value('Code[1]','VARCHAR(max)'))) Code,
    ltrim(rtrim(T.c.value('Name[1]','VARCHAR(max)'))) Name
FROM @xml.nodes('*/*/*/*') AS T(C)
--any rows
SELECT
    ltrim(rtrim(T.c.value('Code[1]','VARCHAR(max)'))) Code,
    ltrim(rtrim(T.c.value('Name[1]','VARCHAR(max)'))) Name
FROM @xml.nodes('GetDictResponse/GetDictResult/Dicts/Dict') AS T(C)

我如何获得 Code和 Name值(value)观？

问候，
RB

Answer 1

试试这个:

DECLARE @strXML XML =
'<GetDictResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <GetDictResult>
        <Success xmlns="http://or.org/ValidationSchema.xsd">true</Success>
        <Dicts xmlns="http://or.org/ValidationSchema.xsd">
        <Dict>
        <Code>TADR</Code>
        <Name>Type of address</Name>
      </Dict>
    </Dicts>
  </GetDictResult>
</GetDictResponse>'

;WITH XMLNAMESPACES('http://or.org/ValidationSchema.xsd' as ns)
    SELECT
        Code = c.value('ns:Code[1]', 'NVARCHAR(20)'),
        Name = c.value('ns:Name[1]', 'NVARCHAR(20)')
    FROM 
        @strXml.nodes('/GetDictResponse/GetDictResult/ns:Dicts/ns:Dict') AS T(C)

一个问题是您拥有的 XML header - 您能摆脱它吗？没有它，此代码确实返回所需的值: