SQL Server
SQL Server
XML Exclusive Canonicalization
See more XML Digital Signatures Examples
Demonstrates how to convert XML to the Exclusive XML Canonicalization form as specified in http://www.w3.org/TR/xml-exc-c14n/Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- The XML digital signature verification class provides utility methods for
-- XML canonicalization. This example demonstrates how to do exclusive XML canonicalization.
-- This example will show many sample XML inputs and outputs. The input XML files are available online
-- as shown in this example.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
DECLARE @canon int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @canon OUT
-- Use exclusive XML canonicalization.
DECLARE @canonVersion nvarchar(4000)
SELECT @canonVersion = 'EXCL_C14N'
DECLARE @withComments int
SELECT @withComments = 0
DECLARE @div1 nvarchar(4000)
SELECT @div1 = '--------------- input ---------------'
DECLARE @div2 nvarchar(4000)
SELECT @div2 = '--------------- canonicalized output ---------------'
DECLARE @sbUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT
DECLARE @i int
SELECT @i = 1
WHILE @i < 20
BEGIN
EXEC sp_OAMethod @sbUrl, 'SetString', @success OUT, 'https://www.chilkatsoft.com/exampleData/c14n/testTESTNUMBER.xml'
DECLARE @numReplaced int
EXEC sp_OAMethod @sbUrl, 'ReplaceI', @numReplaced OUT, 'TESTNUMBER', @i
EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @sTmp0, @sbXml
IF @success = 1
BEGIN
DECLARE @xmlCanon nvarchar(4000)
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @canon, 'CanonicalizeXml', @xmlCanon OUT, @sTmp0, @canonVersion, @withComments
PRINT '---- Test ' + @i + ' ----'
PRINT @div1
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT @div2
PRINT @xmlCanon
PRINT ' '
END
SELECT @i = @i + 1
END
-- The output of this program is:
-- ---- Test 1 ----
-- --------------- input ---------------
-- <?xml version="1.0"?>
--
-- <?xml-stylesheet href="doc.xsl"
-- type="text/xsl" ?>
--
-- <!DOCTYPE doc SYSTEM "doc.dtd">
--
-- <doc>Hello, world!<!-- Comment 1 --></doc>
--
-- <?pi-without-data ?>
--
-- <!-- Comment 2 -->
--
-- <!-- Comment 3 -->
-- --------------- canonicalized output ---------------
-- <?xml-stylesheet href="doc.xsl"
-- type="text/xsl" ?>
-- <doc>Hello, world!</doc>
-- <?pi-without-data?>
--
-- ---- Test 2 ----
-- --------------- input ---------------
-- <?xml version="1.0"?>
--
-- <?xml-stylesheet href="doc.xsl"
-- type="text/xsl" ?>
--
-- <doc>Hello, world!<fish name="goldie" type = "goldfish" color='gold' /></doc>
--
-- <?pi-without-data ?>
--
--
-- --------------- canonicalized output ---------------
-- <?xml-stylesheet href="doc.xsl"
-- type="text/xsl" ?>
-- <doc>Hello, world!<fish color="gold" name="goldie" type="goldfish"></fish></doc>
-- <?pi-without-data?>
--
-- ---- Test 3 ----
-- --------------- input ---------------
-- <doc>
-- <clean> </clean>
-- <dirty> A B </dirty>
-- <mixed>
-- A
-- <clean> </clean>
-- B
-- <dirty> A B </dirty>
-- C
-- </mixed>
-- </doc>
-- --------------- canonicalized output ---------------
-- <doc>
-- <clean> </clean>
-- <dirty> A B </dirty>
-- <mixed>
-- A
-- <clean> </clean>
-- B
-- <dirty> A B </dirty>
-- C
-- </mixed>
-- </doc>
--
-- ---- Test 4 ----
-- --------------- input ---------------
-- <!DOCTYPE doc [
-- <!ATTLIST normId id ID #IMPLIED>
-- <!ATTLIST normNames attr NMTOKENS #IMPLIED>
-- ]>
-- <doc>
-- <text>First line
 Second line</text>
-- <value>2</value>
-- <compute><![CDATA[value>"0" && value<"10" ?"valid":"error"]]></compute>
-- <compute expr='value>"0" && value<"10" ?"valid":"error"'>valid</compute>
-- <norm attr=' '   
	 ' '/>
-- <normNames attr=' A   
	 B '/>
-- <normId id=' '   
	 ' '/>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <text>First line
-- Second line</text>
-- <value>2</value>
-- <compute>value>"0" && value<"10" ?"valid":"error"</compute>
-- <compute expr="value>"0" && value<"10" ?"valid":"error"">valid</compute>
-- <norm attr=" ' 
	 ' "></norm>
-- <normNames attr="A 
	 B"></normNames>
-- <normId id="' 
	 '"></normId>
-- </doc>
--
-- ---- Test 5 ----
-- --------------- input ---------------
-- <doc>
-- <lizard type="gecko & african" abc="& test "" />
-- <snake type="poisonous rattler" />
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <lizard abc="& test "" type="gecko & african"></lizard>
-- <snake type="poisonous rattler"></snake>
-- </doc>
--
-- ---- Test 6 ----
-- --------------- input ---------------
-- <doc>
-- <!-- A namespace node N is ignored if the nearest ancestor element of the node's parent element that is in the node-set has a namespace node in the node-set with the same local name and value as N. -->
-- <animal xmlns="" xmlns:aa="https://www.animal.com/">
-- <reptile xmlns:cc="https://www.herpetology.com/" xmlns:bb="https://www.reptile.com/">
-- <lizard type="african fat tailed" abc="xyz" xmlns:aa="https://www.animal.com/" />
-- <snake type="poisonous rattler" xmlns:bb="https://www.reptile.com/" >
-- <cobra bb:name="benny" />
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
--
-- <animal>
-- <reptile>
-- <lizard abc="xyz" type="african fat tailed"></lizard>
-- <snake type="poisonous rattler">
-- <cobra xmlns:bb="https://www.reptile.com/" bb:name="benny"></cobra>
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- ---- Test 7 ----
-- --------------- input ---------------
-- <doc>
-- <!-- A namespace node N is ignored if the nearest ancestor element of the node's parent element that is in the node-set has a namespace node in the node-set with the same local name and value as N. -->
-- <animal xmlns:aa="https://www.animal.com/">
-- <reptile xmlns:bb="https://www.reptile.com/">
-- <lizard xmlns="" type="african fat tailed" abc="xyz" xmlns:aa="https://www.animal.com/" />
-- <snake type="poisonous rattler" xmlns:bb="https://www.reptile.com/" >
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
--
-- <animal>
-- <reptile>
-- <lizard abc="xyz" type="african fat tailed"></lizard>
-- <snake type="poisonous rattler">
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- ---- Test 8 ----
-- --------------- input ---------------
-- <doc>
-- <!-- generate a space followed by xmlns="" if and only if the following conditions are met:
--
-- the element E that owns the axis is in the node-set
-- The nearest ancestor element of E in the node-set has a default namespace node in the node-set (default namespace nodes always have non-empty values in XPath)
-- -->
-- <animal xmlns="https://www.animal.com/">
-- <reptile xmlns:bb="https://www.reptile.com/">
-- <lizard xmlns="" type="african fat tailed" abc="xyz" xmlns:bb="https://www.reptile222.com/" >
-- <myPet xmlns:bb="https://www.reptile.com/">larry</myPet>
-- </lizard>
-- <snake type="poisonous rattler" xmlns:bb="https://www.reptile.com/" >
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
--
-- <animal xmlns="https://www.animal.com/">
-- <reptile>
-- <lizard xmlns="" abc="xyz" type="african fat tailed">
-- <myPet>larry</myPet>
-- </lizard>
-- <snake type="poisonous rattler">
-- </snake>
-- </reptile>
-- </animal>
-- </doc>
--
-- ---- Test 9 ----
-- --------------- input ---------------
-- <!DOCTYPE doc [<!ATTLIST e9 attr CDATA "default">]>
-- <doc>
-- <e1 />
-- <e2 ></e2>
-- <e3 name = "elem3" id="elem3" />
-- <e4 name="elem4" id="elem4" ></e4>
-- <e5 a:attr="out" b:attr="sorted" attr2="all" attr="I'm"
-- xmlns:b="http://www.ietf.org"
-- xmlns:a="http://www.w3.org"
-- xmlns="http://example.org"/>
-- <e6 xmlns="" xmlns:a="http://www.w3.org">
-- <e7 xmlns="http://www.ietf.org">
-- <e8 xmlns="" xmlns:a="http://www.w3.org">
-- <e9 xmlns="" xmlns:a="http://www.ietf.org"/>
-- </e8>
-- </e7>
-- </e6>
-- </doc>
-- --------------- canonicalized output ---------------
-- <doc>
-- <e1></e1>
-- <e2></e2>
-- <e3 id="elem3" name="elem3"></e3>
-- <e4 id="elem4" name="elem4"></e4>
-- <e5 xmlns="http://example.org" xmlns:a="http://www.w3.org" xmlns:b="http://www.ietf.org" attr="I'm" attr2="all" b:attr="sorted" a:attr="out"></e5>
-- <e6>
-- <e7 xmlns="http://www.ietf.org">
-- <e8 xmlns="">
-- <e9 attr="default"></e9>
-- </e8>
-- </e7>
-- </e6>
-- </doc>
--
-- ---- Test 10 ----
-- --------------- input ---------------
-- <?xml version="1.0" encoding="UTF-8"?>
--
-- <!DOCTYPE note [
-- <!ENTITY nbsp " ">
-- <!ENTITY author "Donald Duck">
-- <!ENTITY writer "Writer: &author;.">
-- <!ENTITY copyright "Copyright: < > Chilkat.">
-- <!ATTLIST OPTIONS
-- FINISH (Metal|Polished|Matte) "Matte"
-- ADAPTER (Included|Optional|NotApplicable) "Included"
-- CASE (HardShell|Soft|NotApplicable) "HardShell">
--
-- <!ATTLIST footer
-- src CDATA #REQUIRED
-- id ID #IMPLIED
-- sort CDATA #FIXED "true"
-- print (yes | no) "yes"
-- >
-- <!ENTITY myEntityA "Some Entity Value A">
-- <!ENTITY myEntityB "Some Entity Value B">
-- ]>
--
-- <note>
-- <OPTIONS/>
-- <to>Tove   á & < > ' " " ' ></to>
-- <from eTest="&   á < > > ' ' " ">Jani</from>
-- <heading myEntityTest="&myEntityA;">Reminder</heading>
-- <body>Don't forget me this weekend!</body>
-- <footer>&writer; ©right;</footer>
-- <footer print="no">&writer; ©right;</footer>
-- <someCData><![CDATA[
-- & < > ' "
-- " ' & < >
-- This is a test.
-- ]]></someCData>
-- <someText>
-- & < > ' "
-- " ' >
-- This is a test.
-- </someText>
-- </note>
-- --------------- canonicalized output ---------------
-- <note>
-- <OPTIONS ADAPTER="Included" CASE="HardShell" FINISH="Matte"></OPTIONS>
-- <to>Tove � � & < > ' " " ' ></to>
-- <from eTest="& � � < > > ' ' " ">Jani</from>
-- <heading myEntityTest="Some Entity Value A">Reminder</heading>
-- <body>Don't forget me this weekend!</body>
-- <footer print="yes" sort="true">Writer: Donald Duck.�Copyright: < > Chilkat.</footer>
-- <footer print="no" sort="true">Writer: Donald Duck.�Copyright: < > Chilkat.</footer>
-- <someCData>
-- &amp; &lt; &gt; &apos; &quot;
-- " ' & < >
-- This is a test.
-- </someCData>
-- <someText>
-- & < > ' "
-- " ' >
-- This is a test.
-- </someText>
-- </note>
--
-- ---- Test 11 ----
-- --------------- input ---------------
-- <?xml version="1.0" encoding="UTF-8"?>
--
-- <!DOCTYPE note [
-- <!ENTITY nbsp " ">
-- <!ENTITY writer "Writer: Donald Duck.">
-- <!ENTITY copyright "Copyright: < > Chilkat.">
-- <!ATTLIST OPTIONS
-- FINISH (Metal|Polished|Matte) "Matte"
-- ADAPTER (Included|Optional|NotApplicable) "Included"
-- CASE (HardShell|Soft|NotApplicable) "HardShell">
--
-- <!ATTLIST footer
-- src CDATA #REQUIRED
-- id ID #IMPLIED
-- sort CDATA #FIXED "true"
-- print (yes | no) "yes"
-- >
-- <!ENTITY myEntityA "Some Entity Value A">
-- <!ENTITY myEntityB "Some Entity Value B">
--
-- <!ENTITY c SYSTEM "http://www.xmlwriter.net/copyright.xml">
-- <!ENTITY c PUBLIC "-//W3C//TEXT copyright//EN"
-- "http://www.w3.org/xmlspec/copyright.xml">
--
-- <!ENTITY logo SYSTEM "http://www.xmlwriter.net/logo.gif" NDATA gif>
-- <!ENTITY logo PUBLIC "-//W3C//GIF logo//EN" "http://www.w3.org/logo.gif" NDATA gif>
--
-- ]>
--
-- <note>
-- <OPTIONS/>
-- <to>Tove</to>
-- <from>Jani</from>
-- <heading myEntityTest="&myEntityA;">Reminder</heading>
-- <body>Don't forget me this weekend!</body>
-- <footer>&writer; ©right;</footer>
-- </note>
-- --------------- canonicalized output ---------------
-- <note>
-- <OPTIONS ADAPTER="Included" CASE="HardShell" FINISH="Matte"></OPTIONS>
-- <to>Tove</to>
-- <from>Jani</from>
-- <heading myEntityTest="Some Entity Value A">Reminder</heading>
-- <body>Don't forget me this weekend!</body>
-- <footer print="yes" sort="true">Writer: Donald Duck.�Copyright: < > Chilkat.</footer>
-- </note>
--
-- ---- Test 12 ----
-- --------------- input ---------------
-- <?xml version="1.0" encoding="UTF-8"?>
-- <lizard xml:space="retain" xml:lang="en">
-- <gecko />
-- <bearded_dragon xml:lang="en" xml:space="retain">pip</bearded_dragon>
-- </lizard>
--
-- --------------- canonicalized output ---------------
-- <lizard xml:lang="en" xml:space="retain">
-- <gecko></gecko>
-- <bearded_dragon xml:lang="en" xml:space="retain">pip</bearded_dragon>
-- </lizard>
--
-- ---- Test 13 ----
-- --------------- input ---------------
-- <?xml version="1.0" encoding="UTF-8"?>
-- <lizard xmlns="http://www.chilkatsoft.com">
-- <gecko name="larry" />
-- <bearded_dragon xmlns="http://www.beardies.com" name="pip">pip</bearded_dragon>
-- </lizard>
--
-- --------------- canonicalized output ---------------
-- <lizard xmlns="http://www.chilkatsoft.com">
-- <gecko name="larry"></gecko>
-- <bearded_dragon xmlns="http://www.beardies.com" name="pip">pip</bearded_dragon>
-- </lizard>
--
-- ---- Test 14 ----
-- --------------- input ---------------
-- <doc>
-- <a xmlns:aa="https://www.test1.org">
-- <b xmlns:aa="https://www.test2.org">
-- <c xmlns:aa="https://www.test1.org">
-- <d xmlns:aa="https://www.test2.org">
-- <e xmlns:aa="https://www.test1.org">
-- </e>
-- </d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <a>
-- <b>
-- <c>
-- <d>
-- <e>
-- </e>
-- </d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- ---- Test 15 ----
-- --------------- input ---------------
-- <doc>
-- <a xmlns:aa="https://www.test1.org">
-- <b xmlns:aa="https://www.test1.org">
-- <c xmlns:aa="https://www.test1.org">
-- <d xmlns:aa="https://www.test1.org">
-- <e xmlns:aa="https://www.test1.org">
-- </e>
-- </d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <a>
-- <b>
-- <c>
-- <d>
-- <e>
-- </e>
-- </d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- ---- Test 16 ----
-- --------------- input ---------------
-- <doc>
-- <a xmlns="">
-- <b xmlns="">
-- <c xmlns="https://www.chilkatsoft.com">
-- <c2 xmlns="https://www.chilkatsoft.com">
-- <d xmlns="">
-- <e xmlns="">
-- </e>
-- </d>
-- </c2>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <a>
-- <b>
-- <c xmlns="https://www.chilkatsoft.com">
-- <c2>
-- <d xmlns="">
-- <e>
-- </e>
-- </d>
-- </c2>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- ---- Test 17 ----
-- --------------- input ---------------
-- <doc>
-- <a xmlns:aa="https://www.testA.com/" xmlns:zz="https://testZ.com" zz:color="blue" xmlns:yy="https://testY.com">
-- <b xmlns="">
-- <c xmlns:aa="https://www.testA.com/" aa:location="Chicago" xmlns:bb="https://www.testB.com/">
-- <bb:d xmlns:aa="https://www.testAA.com/" aa:country="USA" yy:planet="Earth">
-- </bb:d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <a xmlns:zz="https://testZ.com" zz:color="blue">
-- <b>
-- <c xmlns:aa="https://www.testA.com/" aa:location="Chicago">
-- <bb:d xmlns:aa="https://www.testAA.com/" xmlns:bb="https://www.testB.com/" xmlns:yy="https://testY.com" yy:planet="Earth" aa:country="USA">
-- </bb:d>
-- </c>
-- </b>
-- </a>
-- </doc>
--
-- ---- Test 18 ----
-- --------------- input ---------------
-- <doc>
-- <a xmlns:aa="https://www.testA.com/" aa:location="Chicago">
-- <b xmlns:aa="https://www.testA.com/"><!-- aa is not emitted here (using exclusive canon) -->
-- <c xmlns:aa="https://www.testA.com/">test</c><!-- aa is not emitted here (using exclusive canon) -->
-- </b>
-- </a>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
-- <a xmlns:aa="https://www.testA.com/" aa:location="Chicago">
-- <b>
-- <c>test</c>
-- </b>
-- </a>
-- </doc>
--
-- ---- Test 19 ----
-- --------------- input ---------------
-- <doc>
-- <!-- generate a space followed by xmlns="" if and only if the following conditions are met:
--
-- the element E that owns the axis is in the node-set
-- The nearest ancestor element of E in the node-set has a default namespace node in the node-set (default namespace nodes always have non-empty values in XPath)
-- -->
-- <animal xmlns:bb="https://www.reptile.com/">
-- <bb:reptile xmlns="https://www.animal.com/" bb:abc="123">
-- <lizard xmlns="" type="african fat tailed" abc="xyz" xmlns:bb="https://www.reptile222.com/" >
-- <myPet xmlns:bb="https://www.reptile.com/">larry</myPet>
-- </lizard>
-- <snake type="poisonous rattler" xmlns:bb="https://www.reptile.com/" >
-- </snake>
-- </bb:reptile>
-- </animal>
-- </doc>
--
-- --------------- canonicalized output ---------------
-- <doc>
--
-- <animal>
-- <bb:reptile xmlns:bb="https://www.reptile.com/" bb:abc="123">
-- <lizard abc="xyz" type="african fat tailed">
-- <myPet>larry</myPet>
-- </lizard>
-- <snake xmlns="https://www.animal.com/" type="poisonous rattler">
-- </snake>
-- </bb:reptile>
-- </animal>
-- </doc>
--
--
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @canon
EXEC @hr = sp_OADestroy @sbUrl
END
GO