score:0

I wouldn't do this in Transact-SQL. While you CAN process XML within SQL Server, you only want to do it there if you cannot handle the XML at a higher level where you have better (and faster) tools.

Were I thee, I'd write a simple set of classes in C# that subclass XElement, and expose a method named StuffParameters() to facilitate your insert/update statement. Because XML shredding is so much, much easier to do in .Net....

// Typed in the editor -- code needs to be eval'd before attempting to compile!
public class ServerData: XElement
{
    public ServerData(string strServer): base(strServer)
    {
        // Base class initializes XElement with XML, throws error if XML not well-formed
    }

    // Properties
    public string Name    // One example of the two dozen properties you'll need
    {
        get()
        {
            return GetAttribute("name");
        }
    }

    // StuffParameters
    public bool StuffParameters(SqlCommand objCommand)
    {
        // In real life you'd use try...catch, but this is prototype code, right?
        objCommand.Parameters["@ServerName"].Value = Name;
        // ... and so forth
    }
}

Start with your list of server XDocuments; create an instance of this class for each. Create a connection to your database, and a SqlCommand object with your stored procedure. Iterate over your list of these classes, passing the command into each class instance's StuffParameters() method.

When the function returns, call ExecuteNonQuery() on your command, and you have inserted the record into your table.

Less coding, and a much more orthodox way of handling XML--so you don't end up with a head-scratcher method that requires hours of contemplation if you ever have to change it.

Hope this helps you....

score:0

This is actually much simpler than you are expecting it to be. You can easily traverse the XML by using a single .nodes() function and then specifying the child paths in the .value() function:

SELECT n.value('(./name/text())[1]','varchar(15)') AS [name],
       n.value('(./displayName/text())[1]','varchar(15)') AS [displayName],
       n.value('(./comment/text())[1]','varchar(15)') AS [comment],
  n.value('(./logonCredentials/userName/text())[1]','varchar(15)') AS [userName],
      n.value('(./logonCredentials/domain/text())[1]','varchar(15)') AS [domain],
  n.value('(./logonCredentials/password/text())[1]','varchar(15)') AS [password],
       n.value('(./connectionSettings/text())[1]','varchar(15)')
              AS [connectionSettings],
     n.value('(./gatewaySettings/text())[1]','varchar(15)') AS [gatewaySettings],
       n.value('(./remoteDesktop/text())[1]','varchar(15)') AS [remoteDesktop],
       n.value('(./localResources/audioRedirection/text())[1]','varchar(15)')
              AS [audioRedirection],
    n.value('(./localResources/audioRedirectionQuality/text())[1]','varchar(15)')
              AS [audioRedirectionQuality],
    n.value('(./localResources/audioCaptureRedirection/text())[1]','varchar(15)')
              AS [audioCaptureRedirection],
   n.value('(./securitySettings/text())[1]','varchar(15)') AS [securitySettings],
      n.value('(./displaySettings/text())[1]','varchar(15)') AS [displaySettings]
FROM @RDCM.nodes('/group/server') AS s(n)

Full test:

DECLARE @RDCM XML
SET @RDCM = N'<group>
<server>
            <name>111.111.11.11</name>
            <displayName>EVIL SERVER</displayName>
            <comment />
            <logonCredentials inherit="None">
                <userName>user</userName>
                <domain>DOMAIN</domain>
                <password storeAsClearText="True">xxxxxxx</password>
            </logonCredentials>
            <connectionSettings inherit="FromParent" />
            <gatewaySettings inherit="FromParent" />
            <remoteDesktop inherit="FromParent" />
            <localResources inherit="None">
                <audioRedirection>2</audioRedirection>
                <audioRedirectionQuality>2</audioRedirectionQuality>
                <audioCaptureRedirection>0</audioCaptureRedirection>
                <keyboardHook>2</keyboardHook>
                <redirectClipboard>True</redirectClipboard>
                <redirectDrives>True</redirectDrives>
                <redirectPorts>False</redirectPorts>
                <redirectPrinters>False</redirectPrinters>
                <redirectSmartCards>False</redirectSmartCards>
            </localResources>
            <securitySettings inherit="FromParent" />
            <displaySettings inherit="FromParent" />
        </server>
        <server>
            <name>111.12.11.11</name>
            <displayName>NICE SERVER</displayName>
            <comment />
            <logonCredentials inherit="None">
                <userName>user2</userName>
                <domain>DOMAIN2</domain>
                <password storeAsClearText="True">xxxxxxx</password>
            </logonCredentials>
            <connectionSettings inherit="FromParent" />
            <gatewaySettings inherit="FromParent" />
            <remoteDesktop inherit="FromParent" />
            <localResources inherit="FromParent" />
            <securitySettings inherit="FromParent" />
            <displaySettings inherit="FromParent" />
        </server>
        </group>'


SELECT n.value('(./name/text())[1]','varchar(15)') AS [name],
       n.value('(./displayName/text())[1]','varchar(15)') AS [displayName],
       n.value('(./comment/text())[1]','varchar(15)') AS [comment],
n.value('(./logonCredentials/userName/text())[1]','varchar(15)') AS [userName],
    n.value('(./logonCredentials/domain/text())[1]','varchar(15)') AS [domain],
n.value('(./logonCredentials/password/text())[1]','varchar(15)') AS [password],
       n.value('(./connectionSettings/text())[1]','varchar(15)')
              AS [connectionSettings],
   n.value('(./gatewaySettings/text())[1]','varchar(15)') AS [gatewaySettings],
       n.value('(./remoteDesktop/text())[1]','varchar(15)') AS [remoteDesktop],
       n.value('(./localResources/audioRedirection/text())[1]','varchar(15)')
              AS [audioRedirection],
  n.value('(./localResources/audioRedirectionQuality/text())[1]','varchar(15)')
              AS [audioRedirectionQuality],
  n.value('(./localResources/audioCaptureRedirection/text())[1]','varchar(15)')
              AS [audioCaptureRedirection],
 n.value('(./securitySettings/text())[1]','varchar(15)') AS [securitySettings],
    n.value('(./displaySettings/text())[1]','varchar(15)') AS [displaySettings]
FROM @RDCM.nodes('/group/server') AS s(n)

More questions

More questions with similar tag