Monthly Archives: April 2009

SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @authorsXML XML

SET @authorsXML = ‘
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>

[/sql]

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

[sql collapse=”false” firstline=”1″ gutter=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]

(/Author/LastName)[1]

[/sql]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

Read more

SQLXML : How to Merge Two Nodes Using FOR XML PATH

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— ============================================================
— sample code that shows how to merge different nodes
— in the same XML document by using FOR XML PATH()
— Donabel Santos
— ============================================================

— sample XML snippet
DECLARE @sampleXML XML
SET @sampleXML =

<Contacts>
<Contact Type="Main" Value="John Doe">
<Locations>
<Location Type="Headquarters" Address="123 XYZ" City="New Westminster"
Province="BC" Country="CA" PostalCode="V1L1B6" />
</Locations>
<Phones>
<Phone Type="Main" Value="6041112222" />
<Phone Type="Secondary" Value="6041113333" />
<Phone Type="Fax" Value="6045553322" />
</Phones>
<Emails>
<Email Type="Main" Value="john.doe@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
</Emails>
</Contact>

<Contact Type="Secondary" Value="Mary Smith">
<Locations>
<Location Type="Headquarters" Address="123 ABC" City="New Westminster"
Province="BC" Country="CA" PostalCode="V1L1B6" />
</Locations>
<Phones>
<Phone Type="Main" Value="6041112255" />
<Phone Type="Secondary" Value="6041113777" />
<Phone Type="Fax" Value="6045553311" />
</Phones>
<Emails>
<Email Type="Main" Value="mary.smith@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
</Emails>
</Contact>
</Contacts>

— get only the elements underneath <Phones> and <Emails>
SELECT
@sampleXML.query (‘(/Contacts/Contact/Phones/*)’),
@sampleXML.query (‘(/Contacts/Contact/Emails/*)’)
FOR XML PATH(”)

— result
/*
<Phone Type="Main" Value="6041112222" />
<Phone Type="Secondary" Value="6041113333" />
<Phone Type="Fax" Value="6045553322" />
<Phone Type="Main" Value="6041112255" />
<Phone Type="Secondary" Value="6041113777" />
<Phone Type="Fax" Value="6045553311" />
<Email Type="Main" Value="john.doe@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
<Email Type="Main" Value="mary.smith@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
*/

[/sql]

BlackNinja Posts: Invoking Javascript and Impersonating Users in SharePoint

I just wrote a couple of SharePoint posts at the Black Ninja Software blog:

How to Programmatically Impersonate Users in SharePoint
– this post shows how you can programmatically execute code in another user’s context, and you can do this by getting a handle to that user’s UserToken

How to Invoke Javascript Snippets Without Using RegisterClientScriptBlock
– this post shows how to invoke and change Javascript code without needing to register that code. This approach uses asp:Literal

And in case you missed this one:
SharePoint Readiness Checklist – Reposted

SQLXML : How to Use SQL Server XML Function exist()

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

— ============================================================
— sample code on using the SQL Server xml method exist()
— several samples shown
— Donabel Santos
— ============================================================

DECLARE @xmlSnippet XML
DECLARE @id SMALLINT
DECLARE @value VARCHAR(20)

SET @xmlSnippet =

<ninjaElement id="1">SQL Server Ninja</ninjaElement>
<ninjaElement id="2">SharePoint Ninja</ninjaElement>
<ninjaElement id="3">ASP.NET Ninja</ninjaElement>

— this is what we will look for
SET @id = 2
SET @value =’SQL Server Ninja’

— note exist() will return only either :
— 1 (true) or 0 (false)

— check if a node called ninjaElement exists
— at any level in the XML snippet
SELECT @xml.exist(‘//ninjaElement’)

— check if a node called bar exists
SELECT @xml.exist(‘//bar’)

— check if attribute id exists anywhere
SELECT @xml.exist(‘//@id’)

— check if attribute id exists within a ninjaElement tag
SELECT @xml.exist(‘//ninjaElement[@id]’)

— check if the id attribute equals to what we saved
— in the @id variable
SELECT @xml.exist(‘/ninjaElement[@id=sql:variable("@id")]’)

— check if the node text equals to what
— we saved in the @value variable
SELECT @xml.exist(‘/ninjaElement[text()=sql:variable("@value")]’)

[/sql]

SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

— ============================================================
— sample code on joining multiple XML snippets
— using query() and UNION ALL
— Donabel Santos
— ============================================================

— declare xml variables
DECLARE @firstXMLSnippet XML
DECLARE @secondXMLSnippet XML

— first XML snippet
SET @firstXMLSnippet =

<attributes>
<attribute id="13" name="EmpNo">
<item value="10" />
</attribute>
<attribute id="44" name="Position">
<item value="Manager" />
</attribute>
<attribute id="32" name="Address">
<item value="123 XYZ St." />
</attribute>
<attribute id="33" name="City">
<item value="Vancouver" />
</attribute>
</attributes>

— second XML snippet
SET @secondXMLSnippet =

<attribute id="37" name="Province">
<item value="BC" />
</attribute>
<attribute id="52" name="Comment">
<item value="SQL Server XML How To" />
</attribute>

— use query() to extract just the <attribute> elements
— join using UNION ALL
— add the root element back by using ROOT() option
SELECT @firstXMLSnippet.query(‘//attribute’)
UNION ALL
SELECT @secondXMLSnippet
FOR XML PATH (”), ROOT(‘attributes’), TYPE

–result
/*
<attributes>
<attribute id="13" name="EmpNo">
<item value="10" />
</attribute>
<attribute id="44" name="Position">
<item value="Manager" />
</attribute>
<attribute id="32" name="Address">
<item value="123 XYZ St." />
</attribute>
<attribute id="33" name="City">
<item value="Vancouver" />
</attribute>
<attribute id="37" name="Province">
<item value="BC" />
</attribute>
<attribute id="52" name="Comment">
<item value="SQL Server XML How To" />
</attribute>
</attributes>
*/

[/sql]

Looking for another Ninja (that is Ninja talk for looking for a good developer)

Black Ninja Software is looking for another Ninja to join the team.

Shereen has posted the “requirements” – and that really sums up to a few words.

Passion and love for what you’re doing.

We do a lot of SharePoint/SQL Server/ASP.NET, but we’re not necessarily looking for the technical skills. Don’t get me wrong, that is very important. You have to be technically adept. However, we also recognize that the technical skill by itself is a hollow measure of what makes a good addition to the team. The passion and the love for what you’re doing will transform into l33t technical skills, just give it some time.

I used to be a PHP/Perl/bash shell/a-little-bit-of-Python developer. When I first applied to a Microsoft-centric company, I had no qualms saying I didn’t code in C# nor ASP.NET, but that I loved learning, and that I loved to program and to do databases. And that wasn’t lip service. As you get more experience, you also realize that although you’d love to learn all cool languages, you pick a few favorite ones and that’s what you master. I still love programming in PHP, but my passions right now are SQL Server (top spot, no question, no competition), SharePoint and C#/ASP.NET. Throw in a whole lotta CSS and a handful of design/creative work 🙂

trephine commented on the Black Ninja blog (excerpt):

:
100% agree on the list of positive and negative indicators. When I had an opportunity to interview potential engineers, the one question I got the most out of was “If you had enough money that you didn’t have to work, what would you do with your days?”
:

I thought that was a very interesting question. What would you do?
(I posted my comment at the Black Ninja Blog – Looking for another Ninja – check it out and we’d love to hear what you’d do)

Or if you’re interested to become a (cool) Ninja, send us your Ninja-file (contact@blackninjasoftware.com).