RSS

Tag Archives: tsql

SELECT latestversion FROM versionedRows: a comparative approach

Because there’s no “the best way to do things” but only the best way to do THIS thing (to read with Yoda voice).

Advertisements
 
Leave a comment

Posted by on 2017/09/07 in dev

 

Tags: , ,

Extract the XPath of every node from an XML. Uh, in SQL Server

Disclaimer: this is SQL Server 2005 compliant! =O

Ok, we’re on a database and we want tables:

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT 
    ISNULL(id,'') AS id
    , parentid
    , ROW_NUMBER() OVER (
	PARTITION BY parentid,localname
	ORDER BY id
    ) AS idx
    , ROW_NUMBER() OVER (
	PARTITION BY parentid
	ORDER BY id
    ) AS position
    , nodetype
    , CASE
	WHEN (nodetype = 3) THEN 'text()'
	WHEN (nodetype = 2) THEN '@' + localname
	ELSE localname
    END as localname
    , text
  INTO #nodetree
  FROM OPENXML(@idoc, '/', 3)
EXEC sp_xml_removedocument @idoc

ALTER TABLE #nodetree ADD PRIMARY KEY (id)
SELECT * FROM #nodetree

Now let’s use a recursive CTE to build the XPath of every text node

;WITH cte AS (
  SELECT
    parentid
    , id
    , N'/' + localname + '[' + cast(idx as nvarchar(100)) + ']' AS xpath
    , text
  FROM #nodetree WHERE nodetype = 3
  UNION ALL
  SELECT
    parent.parentid
    , node.id
    , N'/' + localname + '[' + cast(idx as nvarchar(100)) + ']' + xpath
    , node.text
  FROM cte AS node
  INNER JOIN #nodetree parent on parent.id = node.parentid
)
SELECT
  REPLACE(xpath, '/text()[1]', '') as xpath, text
  FROM cte
  WHERE parentid IS NULL

Finally check the XPath built:

select 
  node.value(N'insert here one xpath row from the CTE', nvarchar(1000))
  from @xml.nodes(N'.') as T(node)
 
Leave a comment

Posted by on 2016/12/23 in dev

 

Tags: , , ,

tsql: give me 5! (and 6, 7, 8, …)

Sometimes on databases I’ve worked on “table of numbers”, i.e. to store Years.
Are you sure you really need that table? There are a lot of -funny- ways to get numbers from sql! C’mon!

0-999 numbers, using a Table Value Constructor (http://msdn.microsoft.com/en-us/library/dd776382.aspx)

;WITH tmp AS (
  SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS X(y)
)
SELECT
  a.y + (b.y * 10) + (c.y*100) as Value
FROM tmp as a, tmp as b, tmp as c
ORDER BY Value

These are years from 1900 to 3947, is it enough?

select 1900 + number as [Year]
  from master..spt_values
  where type = 'P'

This is a more comprehensive list of numbers between 0 and 65535, aka (256 * 256) – 1

;with seed as (
  select number
  from master..spt_values
  where type = 'P'
  and number < 256
)
select n1.number + n2.number * (256)
  from seed n1
  full outer join seed n2 on 1=1
 
Leave a comment

Posted by on 2016/06/01 in dev

 

Tags: , , , ,

Dynamic sql from Edge.js

Intro

I’m exploring the incredibles potential of the Edge.js library, that permits you to execute .NET (Python, Powershell, ..) code within Node.js.
In particular I’m focusing on the edge-sql module.

The sql module allows you to execute a “select,insert,update,delete,exec” statement against a SQL Server database. All you have to do is to set an environment variable (EDGE_SQL_CONNECTION_STRING) with the connection string, but you can also use a connectionString parameter as we’ll see.

The .func(..) function of edge takes two parameters: language (string) and parameters (object) and returns a function that takes your parameters in input, as an object literal.

For further reading let’s go here.

Snippet time!

This is dynamic sql passed to our database! Whoa!

var edge = require('edge');

var myselect = edge.func('sql', {
  connectionString: "Data Source=.\\DEV;Database=mydb;UID=sa;PWD=mypassword"
  , source: function () {/*
    exec sp_executesql
  */}
});

myselect({stm:"SELECT * FROM sys.databases"}, function (error, result) {
  if (error) throw error;
  console.dir(result);
});
 
Leave a comment

Posted by on 2015/06/04 in dev

 

Tags: , , ,

Navigate a parent-child structure without recursion

This is a tricky query to navigate the whole structure, flattening every parent-child relation at every level, whitout recursion.
It uses an XML to represent the tree, then ‘navigate’ that tree taking for each child node its parent.

This is the structured XML:

declare @x xml = '
<x>
  <node id="1"></node>
  <node id="2">
    <node id="21" />
    <node id="22" />
    <node id="23">
      <node id="231" />
    </node>
  </node>
  <node id="3">
    <node id="31" />
  </node>
</x>
'

We can easily find every level’s node and its parent:

select 
  R.value('@id', 'int') as node
  , R.value('../@id', 'int') as parent
  from @x.nodes('//node') T(R)

Pretty easy, isn’t it?

 
Leave a comment

Posted by on 2013/10/11 in dev

 

Tags: , ,

Namespacing XML into TSQL

Some useful how-to use (and NOT to use) namespaces into the XQuery language.

declare @x xml = 
'<Root xmlns="http://onesample" xmlns:ns1="http://anothersample">
  <Node Id="1">TheNode1</Node>
  <ns1:Node Id="2">TheNode2</ns1:Node>
  <Node Id="3">TheNode3</Node>
</Root>'

-- EX1: no namespace: doesn't find anything
select 
  'EX1'
  , T.R.value('.','nvarchar(20)') Node
from @x.nodes('//Node') T(R)

-- EX2: it finds the default namespaced nodes
;WITH XMLNAMESPACES (default 'http://onesample')
select 
  'EX2'
  , T.R.value('.','nvarchar(20)') Node
from @x.nodes('//Node') T(R)

-- EX3: it finds the ns1 namespaced node
;WITH XMLNAMESPACES ('http://anothersample' AS ns1)
select 
  'EX3'
  , T.R.value('.','nvarchar(20)') Node
from @x.nodes('//ns1:Node') T(R)

-- EX4: two queries for the different namespaces. 
-- Note: here we used a 'general step' to group different paths 
-- (the default and the namespaced)
;WITH XMLNAMESPACES (default 'http://onesample', 'http://anothersample' AS ns1)
select 
  'EX4'
  , T.R.value('.','nvarchar(20)')
from @x.nodes('(//ns1:Node, //Node)') T(R)

--  EX5: ignoring namespaces, find everything
select 
  'EX5'
  , T.R.value('.','nvarchar(20)') Node
from @x.nodes('//*:Node') T(R)

-- 'EX6': alternate namespace syntax
select 
  'EX6'
  , T.R.value('.','nvarchar(20)') Node
from @x.nodes('	declare default element namespace "http://onesample";
		declare namespace ns1="http://anothersample";
		(//ns1:Node, //Node)') T(R)

-- 'EX7': using the openxml statement
declare @xi int
exec sp_xml_preparedocument @xi OUTPUT, @x, '<root xmlns:ns2="http://onesample" xmlns:ns1="http://anothersample" />'
select *
  from openxml(@xi, '/ns2:MyRoot/ns2:Node', 1) 
  with	(
    Id int '@Id'
    , Txt nvarchar(100) 'text()'
  )
exec sp_xml_removedocument @xi
 
Leave a comment

Posted by on 2013/04/02 in dev

 

Tags: , ,