问T中的XML解析
EN

Stack Overflow用户

提问于 2022-06-16 02:57:43

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

我试图在to中解析XML文档。当有一些客户机时，XML文档看起来是这样的：

<?xml version="1.0" encoding="utf-16"?> 
<ArrayOfServerSettings>
   <ServerSettings>
     <Address>10.0.1.1</Address>
     <Nodes>
        <NodeSettings>
            <PortNumber>5000</PortNumber>
            <ClientIds>
                <int>1</int>
                <int>2</int>
                <int>3</int>
            </ClientIds>
        </NodeSettings>
      </Nodes>
   </ServerSettings>
</ArrayOfServerSettings>

如果没有客户，看起来就像

<?xml version="1.0" encoding="utf-16"?> 
<ArrayOfServerSettings>
   <ServerSettings>
     <Address>10.0.1.1</Address>
     <Nodes>
        <NodeSettings>
            <PortNumber>5000</PortNumber>
            <ClientIds />
        </NodeSettings>
      </Nodes>
   </ServerSettings>
</ArrayOfServerSettings>

在第一种情况下，表应该如下所示

Address     PortNumber     ClientId
10.0.1.1      5000           1
10.0.1.1      5000           2
10.0.1.1      5000           3

当没有客户端时，我应该像

Address     PortNumber     ClientId
10.0.1.1      5000           0

这是我的t语句。

declare @xml XML
set @xml = N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfCalculationServerSettings>
   <CalculationServerSettings>
      <Address>10.0.1.1</Address>
      <Nodes>
         <NodeSettings>
            <PortNumber>10009</PortNumber>
            <ClientIds>
               <int>1</int>
               <int>2</int>
               <int>3</int>
            </ClientIds>
         </NodeSettings>
      </Nodes>
  </CalculationServerSettings>

‘

DECLARE @DocID INT 
EXEC sp_xml_preparedocument @DocID OUTPUT, @xml

SELECT *   
FROM
    OPENXML(@DocID, '/ArrayOfServerSettings/ServerSettings/Nodes/NodeSettings/ClientIds/int', 1)
    WITH (
        Address NVARCHAR(256) '../../../../Address[1]',
        PortNumber    INT           '../../PortNumber[1]',
        ClientID      INT           '.'
    ) AS D0

EXEC sp_xml_removedocument @DocID

，但我的方法并不适用于第二个选项

xquery

sql

sql-server

xml

tsql

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-06-16 04:06:13

如果我们可以假设您有有效的XML (您的不是)，并且您只有一个PortNumber节点，那么您可以这样做：

declare @xml XML
set @xml = N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfCalculationServerSettings>
   <CalculationServerSettings>
      <Address>10.0.1.1</Address>
      <Nodes>
         <NodeSettings>
            <PortNumber>10009</PortNumber>
            <ClientIds>
               <int>1</int>
               <int>2</int>
               <int>3</int>
            </ClientIds>
         </NodeSettings>
      </Nodes>
  </CalculationServerSettings>
</ArrayOfCalculationServerSettings>'; --This is missing in your XML

SELECT ACSS.CSS.value('(Address/text())[1]','nvarchar(15)') AS Address,
       ACSS.CSS.value('(Nodes/NodeSettings/PortNumber/text())[1]','int') AS PortNumber, --I assume always the same port number
       ISNULL(CS.I.value('text()[1]','int'),0) AS int
FROM @xml.nodes('ArrayOfCalculationServerSettings/CalculationServerSettings') ACSS(CSS)
     OUTER APPLY ACSS.CSS.nodes('Nodes/NodeSettings/ClientIds/int')CS(I);