我正在嘗試在 T-SQL 中決議 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-sql 陳述句
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
但我的方法不適用于第二種選擇
uj5u.com熱心網友回復：
如果我們可以假設您有有效的 XML（您的不是），并且您只有 1 個 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方法進行進一步的嵌套。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/492713.html