T中的XML解析
T中的XML解析
提问于 2022-06-16 10:57:43
我试图在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，但我的方法并不适用于第二个选项
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-06-16 12: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);如果您有多个端口号，并且有更多的int值，那么您需要对nodes方法做一些进一步的嵌套。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72644734
复制相关文章
相似问题
腾讯云开发者
