Archive for the ‘ SQLXML ’ Category

This is an expansion (and slight variation) of Joe’s answer in StackOverflow regarding the thread Find the maximum consecutive years for each ID’s in a table (Oracle SQL) using SQL Server. I’ve tested this in SQL Server 2014, but should work from SQL Server 2005 onwards where the ranking functions are supported.

The problem Joe solved is not uncommon – i.e. how to get the consecutive years of something (consecutive year sales, consecutive years in school, consecutive years volunteering etc), and his solution is pretty clever.

Let’s assume you are working on a sales database. You have multiple clients who could be purchasing from your store several times a year, or a couple of times every five years. You may want to know what’s the maximum consecutive years they’ve purchased from you. Why? Perhaps in a marketing campaign, you may want to give your loyal customers (purchased in 5 or more consecutive years) a special discount.

Your data may look like the following screenshot. Notice that in this example, Client 00001, 00002 and 00003 purchased only once. Client 00004 purchased several times, and it looks like there were purchases in consecutive years.

See full article here at sqlbelle.com

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server XML Red Gate Ebook, XQuery Labs

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages! :)

VN:F [1.9.22_1171]
Rating: 8.7/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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

Assume this is your XML snippet


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>
'

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:


(/Author/LastName)[1]

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 the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.5/10 (46 votes cast)
VN:F [1.9.22_1171]
Rating: +11 (from 11 votes)

SQLXML : How to Merge Two Nodes Using FOR XML PATH

-- ============================================================
-- 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" />
*/

VN:F [1.9.22_1171]
Rating: 9.8/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

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


-- ============================================================
-- 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")]')



VN:F [1.9.22_1171]
Rating: 7.4/10 (39 votes cast)
VN:F [1.9.22_1171]
Rating: +6 (from 10 votes)

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


-- ============================================================
-- 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>
*/

VN:F [1.9.22_1171]
Rating: 9.6/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

SQLXML : How To Save XML Query Results to a File Using BCP

One way to save your XML query results to the file system is by using bcp (bulk copy program).

Be aware of the following before deciding to use bcp for your regular export requirements:

  • bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
  • Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.

If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.2/10 (26 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 5 votes)

SQLXML : How to Bulk Load XML From a File Using OPENXML

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================
   2: -- Object       : OPENXML1.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/17
   7: -- Last Modified: 2008/10/04
   8: -- Notes        : 
   9: -- ==========================================================================
  10:  
  11: -- bulk load
  12: -- For this example, XML file must be saved in C:\
  13: -- XML file also should specify UTF-8 encoding, ie:
  14: -- <?xml version="1.0" encoding="UTF-8"?>
  15:  
  16: DECLARE @XMLTable TABLE
  17: (
  18:    xmlcol XML
  19: )
  20:  
  21: INSERT INTO @XMLTable(xmlcol)
  22: SELECT
  23:     InvoicesXML
  24: FROM
  25: (
  26:     SELECT * 
  27:     FROM OPENROWSET(BULK 'c:\invoice.xml',SINGLE_BLOB) AS Invoices
  28: ) AS Invoices(InvoicesXML)
  29:  
  30: SELECT *
  31: FROM @XMLTable
  32:  
  33:  

VN:F [1.9.22_1171]
Rating: 9.1/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
`