RSS

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)
Advertisements
 
Leave a comment

Posted by on 2016/12/23 in dev

 

Tags: , , ,

js timestamp

Sometimes while logging js I need to have a timestamp in the beggining of the row. There are libraries to manage timestamps and to print wonderful logs, but sometimes the needs are only to make some console.log while debugging.

This is my one-line way to handle it:

function timestamp() {
  return (new Date).toISOString().match(/([0-9]{2}:[0-9]{2}:[0-9]?[0-9].[0-9]{3})/g)[0];
}

or “keep only the time part of an ISO formatted timestamp”.

 
Leave a comment

Posted by on 2016/12/16 in dev

 

Tags: , ,

youtubing

I was studying (rev-eng) how does YouTube works when I’ve found this post about the get_video_info call.

After some digging and some trying I’ve made a node.js module to make the request and parse the response, without the need for the official API.

Here’s the link:
https://github.com/pste/youtube.get-video-info

 
Leave a comment

Posted by on 2016/07/29 in dev

 

Tags: , , , ,

How-to handle app parameters in Node.js

Let say you have to handle one or more custom parameters in your Node.js app. There are many ways to do that, here are my fab 4.
In these examples I’ll read a debug (on/off) and a port (number) parameters.

UPDATED:
keep an eye on these two modules:

 

Passing them to the interpreter

node app.js --debug --port 8080
var dbg = (process.argv.indexOf('--debug') >= 0);
var port = 0;
if (process.argv.indexOf('--port') >= 0 && (process.argv.indexOf('--port')  +1 < process.argv.length))
  port = process.argv.indexOf('--port') + 1;

 

Adding them to the package.json

 ...
 "config": {
   "port": 8080,
   "debug": "true"
 },
 ...
var dbg = (process.env.npm_package_config_debug == 'true');
var port = process.env.npm_package_config_port || 0;

If you’re using PM2, you also need to create an ecosystem.json file (https://keymetrics.io/2014/06/25/ecosystem-json-deploy-and-iterate-faster/) to correctly handle the package.json stuff.

Using a local json file (params.json)

{
  "debug": "true",
  "port": 8080
}
var pars = require('./params.json');
console.log(pars.port, pars.debug);

 

Using an environment variable (not my preferred …)

export debug=true
export port=8080
var dbg = (process.env.debug == 'true');
var port = process.env.port || 0;
 
Leave a comment

Posted by on 2016/07/07 in dev

 

Tags: , ,

xsd quiz

I recently needed to solve an XSD problem. I needed to define a schema that allows this:

<Element>
  <FIRSTValue>abcde</FIRSTValue>
  <SECONDValue>12345</SECONDValue>
</Element>

where FIRSTValue is not mandatory, SECONDValue is not mandatory but either one of them or both must exists.

I started using a “sequence”

<xs:complexType name="AltType">
    <xs:sequence>
      <xs:element type="xs:string" name="FIRSTValue" minOccurs="0" />
      <xs:element type="xs:string" name="SECONDValue" minOccurs="0" />
    </xs:sequence>
</xs:complexType>

but this allows 0 children…

I then tried an “all”:

<xs:complexType name="AltType">
    <xs:all>
      <xs:element type="xs:string" name="FIRSTValue" minOccurs="0" />
      <xs:element type="xs:string" name="SECONDValue" minOccurs="0" />
    </xs:all>
</xs:complexType>

and this also allows 0 children…

Ok let’s think about a “choice”:

<xs:complexType name="AltType">
    <xs:choice>
      <xs:element type="xs:string" name="FIRSTValue" minOccurs="0" />
      <xs:element type="xs:string" name="SECONDValue" minOccurs="0" />
    </xs:choice>
</xs:complexType>

Ouch! I can’t have BOTH children …

Ok, the choice is surely the way:

<xs:complexType name="AltType">
    <xs:choice>
      <xs:element type="xs:string" name="FIRSTValue"  />
      <xs:element type="xs:string" name="SECONDValue"   />
      <xs:sequence>
        <xs:element type="xs:string" name="FIRSTValue" />
        <xs:element type="xs:string" name="SECONDValue" />
      </xs:sequence>
    </xs:choice>
</xs:complexType>

but this is evalued as a “non deterministic” choice and doesn’t compile.

Finally the solution (because yes, it IS possible). Note: to avoid an empty node I defined a new type of string with minimum length = 1.

  <xs:simpleType name="notEmptyString">
    <xs:restriction base="xs:string">
      <xs:minLength value="1" />
    </xs:restriction>
  </xs:simpleType>

  <xs:complexType name="AltType">
    <xs:choice>
      <xs:sequence>
        <xs:element type="notEmptyString" name="FIRSTValue" />
        <xs:element type="xs:string" name="SECONDValue" minOccurs="0" />
      </xs:sequence>
      <xs:element type="notEmptyString" name="SECONDValue" />
    </xs:choice>
  </xs:complexType>
 
Leave a comment

Posted by on 2016/06/30 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: , , , ,

this.window (& friends)

Open a console in your browser, then verify these:

console.log(this === this.window); // true
console.log(this === this.window.window.window); // true
console.log(this === this.self); // true 
console.log(this === this.frames); // true (also when this.frames.length > 0)

When this.frames.length > 0 you can also:

for (i in this.frames) console.log(this === this.frames[i]) // true,false,true,true,false,.... (so many true because we have "self","window","parent","frames",... The others are $,jQuery,google stuff,...)

console.log(this.window[0] === this.window[1]); // false
console.log(this.window[0] === this); // false (obviously in my test)
console.log(this.window[1] === this); // false (obviously in my test)
console.log(this.window[1] === this.frames[1]); // true
 
Leave a comment

Posted by on 2016/04/07 in dev

 

Tags: , , ,