XML Parse in T-SQL-CodePudding

I am trying to parse XML document inside of T-SQL. XML Document look like when there is some clients:

<?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>

if there is no any clients, it looks like

<?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>

In the first case, the table should look like

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

When there is no client I should parse them like

Address     PortNumber     ClientId
10.0.1.1      5000           0

Here is my t-sql statements

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

But my approach is not working for the second option

CodePudding user response：

If we can assume that you have valid XML (yours is not), and that you only have 1 PortNumber node, you could do this:

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);

If you have multiple port numbers, with more int values, then you'll need to do some further nesting of the nodes methods.