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
- Efficiently Shred Element Centric XML TSQL
- How to shred an relate XML generically defined (without distinct element names)
- TSQL for xml path - How can I include a subquery container element even if the subquery is empty?
- MS TSQL for xml path question regarding xpath element
- TSQL - Dynamic query to retrieve the value of given xml element using provided XPATH
- Display duplicate xml element values in rows in tsql
- How to get a particular attribute from XML element in SQL Server
- The Best Way to shred XML data into SQL Server database columns
- Retrieving XML element name using t-SQL
- How can I get a list of element names from an XML value in SQL Server
- How can I add an attribute to the root element of xml generated by SQL's Select for xml
- Delete nodes with certain value from XML using TSQL
- How do I use column values as xml element names using for xml in SQL Server 2005?
- SQL Server query xml attribute for an element value
- Select XML element in SQL Server
- How to insert an element into xml column not knowing if the tree will already exist?
- Name XML result column of TSQL "for xml explicit"?
- How to get specific element Count in XML variable
- Get the names of attributes from an element in a SQL XML column
- Convert XML data into tsql record for any XML response
- Read Large XML String from TSQL FOR XML Statement in C# .NET
- T SQL For XML PATH Group By as Attribute or Element
- Wrapping inner XML nodes with an outer node in TSQL
- Can the ROOT element in a select statement using FOR XML PATH be set using a variable?
- Sql Server: Selective XML Index not being efficiently used
- TSQL FOR XML EXPLICIT
- TSQL - XML query help
- Get XML element name and the attribute value using SQL XPATH query
- Modify xml element name in SQL Server
- Select element from XML
More questions with similar tag
- How to return local temporary table from generated sql
- Check if the first or second condition exists
- Incorrect syntax near ')', possible incorrect use of substring() or charindex()?
- How to select a value based on the position from a delimited column in SQL server 2012
- copy_to does not work with non-default schemas when trying to write permanent table
- SQL Server varchar(50) and varchar(128) performance difference
- Performance problem on a query
- Calculating daily growth using previous row value in new column - SQL Server
- What is enough to store dates/times in the DB from multiple time zones for accurate calculations?
- What's the Difference Between the Various Char Types in SQL 2008 R2?
- How to save data from summernote into SQL DB
- How to connect angular2 with sqlserver in visual studio code?
- Populate flatXmlDataSetBuilder SQL-SERVER table name containing '-'
- Power BI combine results from two SQL-Server tables
- I am running a web app and want to update my SQL Server user profile database with C# code but no matter what I do I cannot get it to work
- When does data load in ASP .NET control?
- Generating a Unique String by UDFunction
- How use inserted\deleted table in stored procedure?
- sql server login (windows authentication account) from linux client through isql utility
- How will we make the below transformation using PIVOT?
- C# SqlConnection could not be found
- TSQL- How to count number of instances
- Group by Time via a Dynamic Parameter
- Do I have SQL Server 2012 installed correctly?
- How to update the data based on column which has bit data type in T-SQL
- SQL date order by
- SQL Select the first Column to match criteria of Many columns in a single row
- How often should sp_updatestats be called?
- Probleme with create view in SQL Server
- How to convert this query from MySQL to SQL Server?