RSS

Tag Archives: sql server

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 there were “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: , , , ,