MSSQL Query Value From XML Datatype

Query some values from a XML datatype result…

SELECT [datumtijd]
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/"; 
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:ID'
                               ).value('.', 'nvarchar(100)')) AS ID
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:Name'
                               ).value('.', 'nvarchar(100)')) AS Name

      --,[body]
FROM [DATABASE].[dbo].[Logtable] 
WHERE [kolom1] = 'waarde' 
ORDER BY datumtijd DESC

MSSQL Query Session Information

Get info on non-sleeping sessions or open open transactions.

SELECT DB_NAME(database_id) AS dbname, *
FROM sys.dm_exec_sessions WHERE 1=1
AND is_user_process > 0
AND ( open_transaction_count > 0 OR status <> 'sleeping' )
ORDER BY database_id

PowerShell SQL Query Function

The function below can be used to a query to a database. The script returns any error as a result (2 lines).

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

 

MSSQL Select with XML Output

The following query returns a result in XML format. the root1 option (root element) adds a root element named “root element” to the file. The option elements creates elements instead of attributes. The auto option creates standard elements with the table name and column names mapped to elements.

SELECT * FROM [tablename] FOR XML auto, root('rootelement'), elements

Another example with a custum (nested) XML layout…

-- Retrieve instancename and other info from database and place info under element intances.instance...
SELECT   i.instance AS [Name]
       , i.edition AS [Edition]
       , i.patchlevel AS [Patchlevel]
       -- Retrieve info from database table on key instance_id place info under child element Databases...
       , ( SELECT   d.[database] AS [Name]
                  , d.RecoveryModel AS [RecoveryModel] 
                  -- Retrieve ref data from OTAP table...
                  , ( SELECT e.OTAP FROM OTAP e WHERE e.ID_OTAP = d.ID_OTAP ) AS [OTAP]
                  -- Retrieve info from application to which this database belongs...
                  , ( SELECT   a.Applicatienaam AS [Name]
                             , a.AuthenticatieModus As [AuthType]
                      FROM applicatie a WHERE a.ID_applicatie = d.ID_applicatie 
                      FOR XML PATH ('Application'), Type )
           FROM [database] d 
           WHERE d.instance_id = i.instance_id 
           FOR XML PATH ('Database'), Type 
           ) AS [Databases]
FROM [dbo].[instances] i FOR XML PATH('Instance'), root('Instances'), Elements

XML Output:

<instances>
  <instance>
    <name>SERVER1\INSTANCE</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.8888.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database1</name>
        <recoverymodel>SIMPLE</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application1</name>
          <authtype>SQL Server</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
  <instance>
    <name>SERVER2</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.9999.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database2</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
      <database>
        <name>Database3</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Testing</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
</instances><instances>
</instances>