Just another WordPress.com site

MS SQL — T-SQL

How to Read XML Data in a SQL Table

We have a XML file which contain the server access logs, we need to identify the users who connected to the SFTP server on a certain date.

The format of the xml file is as below:

Steps:

  1. Create a table dbo.tablexml which contains an xml datatype column

  2. Import the XML file into xmlcl using the openrowset function as shown below.

INSERT INTO dbo.tablexml(xmlcl)
SELECT * FROM OPENROWSET
(BULK 'I:\xml\FileDrop\dailylogfile_export.xml', SINGLE_BLOB) AS x;

Note: The SINGLE_BLOB keyword imports the entire XML file to the XML datatype column.

You can then query the xmlcl as the regular column.

Select * from dbo.tablexml
  1. Now let us query the XML data from the table to produce a transact SQL like result set. Execute the below XQuery as shown below.

SELECT
       pref.value('(log_time/text())[1]', 'varchar(20)') as logtime
       ,pref.value('(description/text())[1]', 'varchar(250)')  as   description
       ,pref.value('(service/text())[1]', 'varchar(25)') as service
       ,pref.value('(sessionid/text())[1]', 'varchar(25)') as sessionid
       ,pref.value('(type/text())[1]', 'varchar(10)') as type
       ,pref.value('(severity/text())[1]', 'varchar(2)') as severity
       ,pref.value('(user/text())[1]', 'varchar(100)') as 'user'
       ,pref.value('(host/text())[1]', 'varchar(20)') as host
       ,pref.value('(lstnconnaddr/text())[1]', 'varchar(50)') as lstnconnaddr
       ,pref.value('(cliconnaddr/text())[1]', 'varchar(50)') as cliconnaddr
       ,pref.value('(sguid/text())[1]', 'varchar(100)') as sguid
FROM   tablexml cross apply
      xmlcl.nodes('/log/entry') logdata(pref)

nodes() function

The XML data type in SQL Server defines a number of methods that can be called on it. One of these is “nodes” – and what this does is basically select a list of XML nodes that match an XQuery expression. Knowing this, look at above statement:

      xmlcl.nodes('/log/entry') logdata(pref)

This is using the path “/log/entry’” to ensure that all “entry” nodes that exist under the “log” are selected. The result is aliased as a table named logdata, and each resulting XML node will be output as a separate row, in the “pref” column.

value() function

The value() function to extract a specific value from the XML, and convert it to a SQL Server data type;

       pref.value('(user/text())[1]', 'varchar(100)') as 'user'

text() function

The “text()” function here retrieves the inner text from within the XML “Name” node.

[1] suffix

The “[1]” suffix acts as an indexer, and fetches the first result matched. In our case, there’s only one name node per entry, but we still need to make sure SQL Server knows this too.

Finally, the second argument is the data type it should be converted to.

Reference:

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Advertisements

Query ADSI

Active Directory Service Interfaces, also known as ADSI, is a set of COM interfaces used to access the directory services to retrieve data. Though it is predominantly used by network administrators and system administrators, there are situations where SQL Server Database administrators or the application that uses SQL Server as the backend needs to get data from ADSI.
This article illustrates how to use SQL Server to access and retrieve data from Active Directory, although we can import active directory data through SSIS, there are many limitation.

1. Limitation in datatype (ntext) and length;

2. Some fields(eg: GUID and Description) are brought in as System.Object[].

The easiest way for importing from AD is still trough the T-sql although it is not easy to handle the date fields.

Sample T-Sql Script:

–Enable distributed queries

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE

GO

EXEC sp_configure ‘ad hoc distributed queries’, 1

RECONFIGURE

–Create linked server

if exists(select srvname from master..sysservers where srvname=‘ADSI’) begin

EXEC sp_droplinkedsrvlogin ‘ADSI’,null

EXEC sp_dropserver ‘ADSI’

end

GO

EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’

EXEC sp_addlinkedsrvlogin ‘ADSI’,true

GO

SELECT CAST(objectGUID as Uniqueidentifier) [GUID]

, samaccountname as Login

, UserPrincipalName as PrincipalName

, givenname as FirstName

, sn as LastName

, mail as Email

, cast(uSNChanged as bigint) as ChangeSequenceNumber

, cast(uSNCreated as bigint) as CreateSequenceNumber

, Company

, Division

, Department

, Title

, StreetAddress

, l as City

, st as Province

, postalCode

, c as Country

, TelephoneNumber as Phone

, facsimileTelephoneNumber as Fax

, mobile

, ipPhone

into #ADSI

–Query AD from OpenRowSet

FROM OPENQUERY(ADSI,

‘SELECT ObjectGuid

, samaccountname

, UserPrincipalName

, sn

, givenname

, mail

, uSNChanged

, uSNCreated

, Company

, Division

, Department

, Title

, StreetAddress

, l

, st

, postalCode

, c

, TelephoneNumber

, facsimileTelephoneNumber

, mobile

, ipPhone

FROM ”LDAP://Market /DC=market,DC=financial,DC=local”

WHERE objectCategory = ”person” AND objectClass = ”user” and UserAccountControl = ”512”’)

where sn is not null and givenname is not null and mail is not null

select * from #ADSI

AD Attributes:
* mAPIRecipient:
Indicates that a contact is not a domain user
* MailNickname
Normally this is the same value as the SamAccountName, but could be different if you wished. Needed for mail enabled contacts.
* SMTP
@ e-mail address. Note that SMTP is case sensitive. All capitals means the default address.
* HomeMDB
Here is where you set the MailStore
* homeDrive
Home Folder : connect. Tricky to configure
* Initials
Useful in some cultures.
* PhysicalDeliveryOfficeName
Office
* SamAccountName
This is a mandatory property, SamAccountName = guyt. The old NT 4.0 logon name, must be unique in the domain.
* UserAccountControl
Used to disable an account.
• 512 – Enable Account
• 514 – Disable account
• 544 – Account Enabled – Require user to change password at first logon
• 546 – Account Enabled – Require user to change password at first logon
• 4096 – Workstation/server
• 66048 – Enabled, password never expires
• 66050 – Disabled, password never expires
• 66080 – Enables, password never expires, password not required.
• 66082 – Disabled, password never expires, password not required.
• 532480 – Domain controller
• 590336 – Enabled, User Cannot Change Password, Password Never Expires

* UserPrincipalName
Often abbreviated to UPN, and looks like an email address. Very useful for logging on especially in a large Forest. Note UPN must be unique in the forest.
* wwwHomePage
User’s home page.
* AccountExpires
The property value is represented using the ADSI IADsLargeInteger. The IADsLargeInteger from the ADSI call represents the number of 100 nanosecond intervals since Jan 1, 1601 (UTC) and “value of 0 or 0x7FFFFFFFFFFFFFFF (9223372036854775807) indicates that the account never expires”.

References:

Attributes for Active Directory Users
User Attributes – Inside Active Directory
Query Active Directory Data from SQL Server using T-SQL


Number of times a particular character appears in a string

 You can do that using replace and len. Count number of x characters in str:
 eg:
  
 select
 Data,
 LEN(Data)-LEN(REPLACE(Data,'"','')) As NumOfQuotes,
 LEN(Data)-LEN(REPLACE(Data,',','')) As NumOfComas
 from Staging.SourceADData

Reference Link: http://stackoverflow.com/questions/9789225/number-of-times-a-particular-character-appears-in-a-string

ASCII Table and Values

Ascii Table

Note: Enter or Change Line character is CR(Carriage return), the Dec# is 13.

Extended ASCII Codes

EBCDIC and IBM Scan Codes

In T-SQL, we can always use function ASCII(SpecialCharacter) to get the ASCII dec values of the special characters, and use Char(ASCIICode) to get the characters.


declare @Column
set @Column=’abcd’+CHAR(13)+’erwehjk$^& \ |’
select @Column
set @Column=replace(REPLACE(@Column,CHAR(13),’@’ ),CHAR(9),’–>’)
select @Column


Check existance of temp table

IF OBJECT_ID(‘tempdb..#tmpTable1’) IS NOT NULL

DROP TABLE #tmpTable1


Stored Procedure with multiple output parameters and query result

We sometimes want to get a query result of a dataset and a few values as well, we can use a Stored Procedure with multiple output parameters.

Eg:

Create PROCEDURE [dbo].[GetQueryWithMultipleOutputs]

(

@startdate datetime,

@enddate datetime,

@minFY varchar(16) output,

@maxFY varchar(16) output,

@pp varchar(2) output,

@pEnddt varchar(20) output

)

AS

select * from dbo.prog

select @minFY=min(FiscalYear),@maxFY=max(FiscalYear),@pp=max(FiscalPeriod), @pEnddt =convert(varchar(20),max(FiscalPeriodEndDate),107)  from #FactData

RETURN; 

GO

SET NOCOUNT OFF

But if you execute the SP as below, it only shows the result set.

exec [dbo].[GetQueryWithMultipleOutputs] ‘2011-04-01′,’2013-03-31’, output, output, output, output

To display both query result, you need to pass the output parameters to local variables:

declare

@a varchar(16) ,

@b varchar(16) ,

@c varchar(2) ,

@d varchar(20) 

exec [dbo].[GetQueryWithMultipleOutputs] ‘2011-04-01′,’2013-03-31’, @a output, @b output, @c output, @d output 

select @a, @b, @c, @d

Note: this kind of Stored Procedures cannot be used in SSRS.


How to create multiple CTEs in one query

Put a semi-colon infront of the first CTE, and a comma infront of following CTES.

eg:

;with CTETest1(Col1,Col2,Col3)

AS

select …

,

with CTETest2(Col11, Col12, Col13, Col14)

AS

select …

,

with CTETest3(Col31, Col32, Col33)

AS

select…