Select from XML

Details
Written by: Stanko Milosev
Category: MS SQL
Hits: 2132
One example on how to select values from XML in MS SQL: 
declare @myXml xml = 
'
<params>
    <param>
        <value>
            <array>
                <data>
                    <value>
                        <string>NameOfValueOne</string>
                    </value>
                    <value>
                        <string>ValueOne</string>
                    </value>
                </data>
            </array>
        </value>
    </param>
    <param>
        <value>
            <array>
                <data>
                    <value>
                        <string>NameOfValueTwo</string>
                    </value>
                    <value>
                        <string>ValueTwo</string>
                    </value>
                </data>
            </array>
        </value>
    </param>
</params>
'

select 
	T.N.value('.', 'varchar(max)') Value
from @myXml.nodes('/params/param/value/array/data/value') T(N)
One more example: 
declare @myXml xml = 
'
    <param>
        <value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value>
        <value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value>
    </param>
    <param>
        <value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value>
        <value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value>
    </param>
'

select 
	T.N.value('(value[@Name="TestNameValue1"]/@Value)[1]', 'varchar(max)') TestNameValue1
	, T.N.value('(value[@Name="TestNameValue2"]/@Value)[1]', 'varchar(max)') TestNameValue2
from @myXml.nodes('/param') T(N)

Another example with OpenXML: 

declare @myXml xml =
'
    <params>
		<param>
			<value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value>
			<value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value>
		</param>
		<param>
			<value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value>
			<value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value>
		</param>
    </params>
'
 
declare @myXmlDoc int
exec sp_xml_preparedocument @myXmlDoc out, @myXml, '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

select TestNameValue1, 
       TestNameValue2
from openxml(@myXmlDoc, '/params/param', 2) 
  with (
         TestNameValue1  varchar(max) '(value[@Name="TestNameValue1"]/@Value)[1]'
		, TestNameValue2 varchar(max) '(value[@Name="TestNameValue2"]/@Value)[1]'
       )

exec sp_xml_removedocument @myXmlDoc

Here have I found:

As far as performance differences between OPENXML and the XML column type with the nodes() function, there doesn't seem to be a crystal clear answer. If you scan through the SQL Server XML posts on the internet you will find some threads where OPENXML is faster than the XML column's nodes() function and vice-versa. As always the best approach may vary depending on your individual circumstances so don't blindly choose the XML column type and the nodes() function over OPENXML.

Log in trigger

Details
Written by: Stanko Milosev
Category: MS SQL
Hits: 3600
One my example how to log messages in trigger.

Create table: 

create table log (tableName varchar(max), msg varchar(max))
Log in trigger: 
declare @xml XML

set @xml = (select * from deleted for xml auto, elements )
insert into log(tableName, msg) values('deleted', cast(@xml as varchar(max)))

Drop if exists

Details
Written by: Stanko Milosev
Category: MS SQL
Hits: 2732
Here are few "DROP IF EXISTS" SQL statements, for my future reference.

Trigger from sys.objects: 

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'myTrigger' AND [type] = 'TR')
BEGIN
      DROP TRIGGER [dbo].[myTrigger];
END;
GO
Stored procedure with OBJECT_ID: 
IF OBJECT_ID('dbo.mySP') IS NOT NULL
  DROP PROCEDURE dbo.mySP
GO
Temp table: 
IF OBJECT_ID('tempdb..#myTempTable') IS NOT NULL DROP TABLE #myTempTable
GO

The login already has an account under a different user name.

Details
Written by: Stanko Milosev
Category: MS SQL
Hits: 9343

If you receive error like:

The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)

Then just take databse offline and back online. Frome here.

  1. Access SQL Server from other machines
  2. How to change sa password.
  3. Query to Find Column From All Tables of Database
  4. Select all from all tables in DB

Subcategories

C#

Azure

ASP.NET

JavaScript

Software Development Philosophy

MS SQL

IBM WebSphere MQ

MySQL

Joomla

Delphi

PHP

Windows

Life

Lazarus

Downloads

Android

CSS

Chrome

HTML

Linux

Eclipse

Page 78 of 164