Working with XML in SQL Server and C#

Simple approach how to deserialize XML to C# object without attributes

Prepare XML string

string xmlString = "<Products><Product><Id>1</Id><Name>My XML product</Name></Product><Product><Id>2</Id><Name>My second product</Name></Product></Products>";




Prepare C# object


public class Product

{

    public int Id { get; set; }

    public string Name { get; set; }

}






Create XML serializer

XmlSerializer serializer = new XmlSerializer(typeof(List<Product>), new XmlRootAttribute("Products"));




Create StringReader object

StringReader stringReader = new StringReader(xmlString);


 

Finally, deserialize to your C# object

List<Product> productList = (List<Product>)serializer.Deserialize(stringReader);

---------------------------------------------------------------------------------------------------------------------

SELECT
P.PostId,
P.Title,
STUFF((
    SELECT DISTINCT ',' + C.Name
    FROM PostCategories AS PC
    INNER JOIN Categories AS C ON PC.CategoryId = C.CategoryId
    WHERE PC.PostId = P.PostId
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Categories
FROM Posts AS P

which will return something like this:

PostId	Title	Categories
1	Composer tutorial	IT
2	Going agile	IT,Management
3	XSL your XML	IT

-- Cleanup

IF OBJECT_ID('dbo.PostCategories', 'U') IS NOT NULL DROP TABLE dbo.PostCategories
GO

IF OBJECT_ID('dbo.Posts', 'U') IS NOT NULL DROP TABLE dbo.Posts
GO

IF OBJECT_ID('dbo.Authors', 'U') IS NOT NULL DROP TABLE dbo.Authors
GO

IF OBJECT_ID('dbo.Categories', 'U') IS NOT NULL DROP TABLE dbo.Categories
GO

-- Schema
CREATE TABLE dbo.Authors (
    AuthorId INT IDENTITY(1, 1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    BirthDate DATE
)
GO

CREATE TABLE dbo.Posts (
    PostId INT IDENTITY(1, 1) PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL,
    AuthorId INT NOT NULL,
    CONSTRAINT FK_Posts_Authors FOREIGN KEY (AuthorId) REFERENCES dbo.Authors (AuthorId) ON DELETE CASCADE ON UPDATE CASCADE
)
GO

CREATE TABLE dbo.Categories (
    CategoryId INT IDENTITY(1, 1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
)
GO

CREATE TABLE dbo.PostCategories (
    PostId INT NOT NULL,
    CategoryId INT NOT NULL,
    CONSTRAINT FK_PostCategory_Posts FOREIGN KEY (PostId) REFERENCES dbo.Posts (PostId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_PostCategory_Categories FOREIGN KEY (CategoryId) REFERENCES dbo.Categories (CategoryId) ON DELETE CASCADE ON UPDATE CASCADE
)
GO

--Data
SET IDENTITY_INSERT dbo.Authors ON
INSERT INTO Authors (AuthorId, FullName, BirthDate) VALUES
    (1, 'Alexandr Marchenko', '1985-03-11'),
    (2, 'Maria Marchenko', '1988-06-11')
SET IDENTITY_INSERT dbo.Authors OFF
GO

SET IDENTITY_INSERT dbo.Categories ON
INSERT INTO Categories (CategoryId, Name) VALUES (1, 'IT'), (2, 'Management')
SET IDENTITY_INSERT dbo.Categories OFF
GO

SET IDENTITY_INSERT dbo.Posts ON
INSERT INTO Posts (PostId, Title, AuthorId) VALUES
    (1, 'Composer tutorial', 1),
    (2, 'Going agile', 2),
    (3, 'XSL your XML', 1)
SET IDENTITY_INSERT dbo.Posts OFF
GO

INSERT INTO PostCategories (PostId, CategoryId) VALUES (1, 1), (2, 1), (2, 2), (3, 1)
GO

yuml.me definition:

[Authors|AuthorId:int;FullName:string;BirthDate:date]
[Posts|PostId:int;Title:string;AuthorId:int]
[Categories|CategoryId:int;Name:string]
[PostCategories|PostId:int;CategoryId:int]
[Authors]1->*[Posts]
[Posts]1->*[PostCategories]
[Categories]1->*[PostCategories]

Map SQL query XML result to C# object

Suppose we have following class:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }
}

SQL Query: SELECT P.* FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost') will return XML:

<ArrayOfPost>
    <Post>
    <PostId>1</PostId>
    <Title>Composer tutorial</Title>
    <AuthorId>1</AuthorId>
    </Post>
    <Post>
    <PostId>2</PostId>
    <Title>Going agile</Title>
    <AuthorId>2</AuthorId>
    </Post>
    <Post>
    <PostId>3</PostId>
    <Title>XSL your XML</Title>
    <AuthorId>1</AuthorId>
    </Post>
</ArrayOfPost>

Notice how we named root node of xml as ArrayOfPost it is used by C# xml serializer while deserializing XML into objects to determine that we wish to get List<Post>

Here is how to deserialize that query into List<Post>:

XmlSerializer xmlSerializer = new XmlSerializer(typeof(List<Post>));
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
    using (var command = new SqlCommand("SELECT P.* FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')", connection))
    {
        connection.Open();
        using (var reader = command.ExecuteXmlReader())
        {
            foreach (var post in (List<Post>)xmlSerializer.Deserialize(reader))
            {
                Console.WriteLine("Id: {0:N0}, Title: {1}", post.PostId, post.Title);
            }
        }
    }
}

Joining objects together

Lets modify our object to be like this:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
}

Id did replace public int AuthorId { get; set; } with public Author Author { get; set; }

Now we are going to ask our database for more information with this query:

SELECT
P.PostId,
P.Title,
(SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author
FROM Posts AS P
FOR XML PATH('Post'), ROOT('ArrayOfPost')

which will return:

<ArrayOfPost>
    <Post>
    <PostId>1</PostId>
    <Title>Composer tutorial</Title>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    </Post>
    <Post>
    <PostId>2</PostId>
    <Title>Going agile</Title>
    <Author>
        <AuthorId>2</AuthorId>
        <FullName>Maria Marchenko</FullName>
        <BirthDate>1988-06-11</BirthDate>
    </Author>
    </Post>
    <Post>
    <PostId>3</PostId>
    <Title>XSL your XML</Title>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    </Post>
</ArrayOfPost>

And mapping from previous example should still work (just make sure to chage query):

XmlSerializer xmlSerializer = new XmlSerializer(typeof(List<Post>));
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
    using (var command = new SqlCommand("SELECT P.PostId, P.Title, (SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')", connection))
    {
        connection.Open();
        using (var reader = command.ExecuteXmlReader())
        {
            foreach (var post in (List<Post>)xmlSerializer.Deserialize(reader))
            {
                Console.WriteLine("Id: {0:N0}, Title: {1}, Author: {2} ({3:yyyy\\-MM\\-dd})", post.PostId, post.Title, post.Author.FullName, post.Author.BirthDate);
            }
        }
    }
}

will output:

Id: 1, Title: Composer tutorial, Author: Alexandr Marchenko (1985-03-11)
Id: 2, Title: Going agile, Author: Maria Marchenko (1988-06-11)
Id: 3, Title: XSL your XML, Author: Alexandr Marchenko (1985-03-11)

IT IS AWESOME!

Joining multiple objects

Lets modify our class again:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public string[] Categories { get; set; }
}

Now I have added public string[] Categories { get; set; } notice that it is just array of strings, in following example we will join categories itself to post object.

For C# xml serializer to be able to deserialize strings array xml should look something like: <acme><string>foo</string><string>bar</string></acme>.

So here is modified SQL:

SELECT
P.*,
(SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author,
(
    SELECT C.Name AS string
    FROM PostCategories AS PC
    INNER JOIN Categories AS C ON PC.CategoryId = C.CategoryId
    WHERE PC.PostId = P.PostId
    FOR XML PATH(''), TYPE
) AS Categories
FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')

And its ouput:

<ArrayOfPost>
    <Post>
    <PostId>1</PostId>
    <Title>Composer tutorial</Title>
    <AuthorId>1</AuthorId>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    <Categories>
        <string>IT</string>
    </Categories>
    </Post>
    <Post>
    <PostId>2</PostId>
    <Title>Going agile</Title>
    <AuthorId>2</AuthorId>
    <Author>
        <AuthorId>2</AuthorId>
        <FullName>Maria Marchenko</FullName>
        <BirthDate>1988-06-11</BirthDate>
    </Author>
    <Categories>
        <string>IT</string>
        <string>Management</string>
    </Categories>
    </Post>
    <Post>
    <PostId>3</PostId>
    <Title>XSL your XML</Title>
    <AuthorId>1</AuthorId>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    <Categories>
        <string>IT</string>
    </Categories>
    </Post>
</ArrayOfPost>

Our C# code will be able to deserialize this without changes again.

XmlSerializer xmlSerializer = new XmlSerializer(typeof(List<Post>));
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
    using (var command = new SqlCommand("SELECT P.*, (SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author, (SELECT C.Name AS string FROM PostCategories AS PC INNER JOIN Categories AS C ON PC.CategoryId = C.CategoryId WHERE PC.PostId = P.PostId FOR XML PATH(''), TYPE) AS Categories FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')", connection))
    {
        connection.Open();
        using (var reader = command.ExecuteXmlReader())
        {
            foreach (var post in (List<Post>)xmlSerializer.Deserialize(reader))
            {
                Console.WriteLine(
                    "[{0:N0}] {1} by {2} ({3:yyyy\\-MM\\-dd}) in {4}",
                    post.PostId,
                    post.Title,
                    post.Author.FullName,
                    post.Author.BirthDate,
                    string.Join(", ", post.Categories)
                );
            }
        }
    }
}

Will output:

[1] Composer tutorial by Alexandr Marchenko (1985-03-11) in IT
[2] Going agile by Maria Marchenko (1988-06-11) in IT, Management
[3] XSL your XML by Alexandr Marchenko (1985-03-11) in IT

Joining List of objects

Here is last example:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public Category[] Categories { get; set; }
}

I did replace public string[] Categories { get; set; } with public Category[] Categories { get; set; } so now we wanna get list of categories rather than list of category names.

Here is SQL query:

SELECT
P.*,
(SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author,
(
    SELECT *
    FROM PostCategories AS PC
    INNER JOIN Categories AS C ON PC.CategoryId = C.CategoryId
    WHERE PC.PostId = P.PostId
    FOR XML PATH('Category'), TYPE
) AS Categories
FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')

And its output:

<ArrayOfPost>
    <Post>
    <PostId>1</PostId>
    <Title>Composer tutorial</Title>
    <AuthorId>1</AuthorId>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    <Categories>
        <Category>
        <PostId>1</PostId>
        <CategoryId>11</CategoryId>
        <Name>IT</Name>
        </Category>
    </Categories>
    </Post>
    <Post>
    <PostId>2</PostId>
    <Title>Going agile</Title>
    <AuthorId>2</AuthorId>
    <Author>
        <AuthorId>2</AuthorId>
        <FullName>Maria Marchenko</FullName>
        <BirthDate>1988-06-11</BirthDate>
    </Author>
    <Categories>
        <Category>
        <PostId>2</PostId>
        <CategoryId>11</CategoryId>
        <Name>IT</Name>
        </Category>
        <Category>
        <PostId>2</PostId>
        <CategoryId>22</CategoryId>
        <Name>Management</Name>
        </Category>
    </Categories>
    </Post>
    <Post>
    <PostId>3</PostId>
    <Title>XSL your XML</Title>
    <AuthorId>1</AuthorId>
    <Author>
        <AuthorId>1</AuthorId>
        <FullName>Alexandr Marchenko</FullName>
        <BirthDate>1985-03-11</BirthDate>
    </Author>
    <Categories>
        <Category>
        <PostId>3</PostId>
        <CategoryId>11</CategoryId>
        <Name>IT</Name>
        </Category>
    </Categories>
    </Post>
</ArrayOfPost>

Notice that we are named our node Categories and not ArrayOfCategories it will work like this.

C# code:

XmlSerializer xmlSerializer = new XmlSerializer(typeof(List<Post>));
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
    using (var command = new SqlCommand("SELECT P.*, (SELECT * FROM Authors AS A WHERE A.AuthorId = P.AuthorId FOR XML PATH(''), TYPE) AS Author, (SELECT * FROM PostCategories AS PC INNER JOIN Categories AS C ON PC.CategoryId = C.CategoryId WHERE PC.PostId = P.PostId FOR XML PATH('Category'), TYPE ) AS Categories FROM Posts AS P FOR XML PATH('Post'), ROOT('ArrayOfPost')", connection))
    {
        connection.Open();
        using (var reader = command.ExecuteXmlReader())
        {
            foreach (var post in (List<Post>)xmlSerializer.Deserialize(reader))
            {
                Console.WriteLine(
                    "[{0:N0}] {1} by {2} ({3:yyyy\\-MM\\-dd}) in {4}",
                    post.PostId,
                    post.Title,
                    post.Author.FullName,
                    post.Author.BirthDate,
                    string.Join(", ", post.Categories.Select(c => c.Name))
                );
            }
        }
    }
}

output will be exactly the same as in previous example.

Workflow

If you ever got into troubles all you need to do is to try serialize/deserialize objects by hand, look at produced XML and try to reproduce it in SQL query

Serialize C# object to XML

XmlSerializer xmlSerializer = new XmlSerializer(typeof(Post));
StringBuilder stringBuilder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(stringBuilder))
{
    xmlSerializer.Serialize(xmlWriter, new Post { PostId = 1, Title = "Composer tutorial", AuthorId = 1 });
    Console.WriteLine(stringBuilder.ToString());
}

will output:

<?xml version="1.0" encoding="utf-16"?>
<Post xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <PostId>1</PostId>
        <Title>Composer tutorial</Title>
        <AuthorId>1</AuthorId>
</Post>

Deserialize C# object from XML

using (TextReader reader = new StringReader("<?xml version=\"1.0\" encoding=\"utf-16\"?><Post><PostId>1</PostId><Title>Composer tutorial</Title><AuthorId>1</AuthorId></Post>"))
{
    var post = (Post)xmlSerializer.Deserialize(reader);
    Console.WriteLine("Id: {0:N0}, Title: {1}, Author: {2:N0}", post.PostId, post.Title, post.AuthorId);
}

Will output:

Id: 1, Title: Composer tutorial, Author: 1

Notice that we are ommited XML namespaces as they seems to be not required

one more addition:

Saving C# object to SQL via XML

Stored procedure template:

CREATE PROCEDURE dbo.SavePost @post XML
AS
BEGIN
    MERGE dbo.Posts AS target
    USING (
        SELECT
            Post.value('(PostId/text())[1]','INT') AS PostId,
            Post.value('(Title/text())[1]','NVARCHAR(100)') AS Title,
            Post.value('(AuthorId/text())[1]','INT') AS AuthorId
        FROM
            @post.nodes('/Post')AS TEMPTABLE(Post)
    ) AS source (PostId, Title, AuthorId)
    ON (target.PostId = source.PostId)
    WHEN MATCHED THEN
        UPDATE SET Title = source.Title, AuthorId = source.AuthorId
    WHEN NOT MATCHED THEN
        INSERT (Title, AuthorId)
        VALUES (source.Title, source.AuthorId);
END
GO

C# code sample:

var xmlSerializer = new XmlSerializer(typeof(Post));
var stringBuilder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(stringBuilder))
{
    xmlSerializer.Serialize(xmlWriter, new Post { PostId = 2, Title = "Stored Procedure XML", AuthorId = 1 });
}
Console.WriteLine(stringBuilder.ToString());

using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
    using (var command = new SqlCommand("SavePost", connection) { CommandType = System.Data.CommandType.StoredProcedure })
    {
        command.Parameters.Add(new SqlParameter("@post", stringBuilder.ToString()));
        connection.Open();
        var numberOfAffectedRows = command.ExecuteNonQuery();
        Console.WriteLine("Number of affected rows: {0:N0}", numberOfAffectedRows);
    }
}

Example from: http://www.aspsnippets.com/Articles/Pass-XML-parameter-to-Stored-Procedure-in-C-and-VBNet.aspx

CREATE PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
        SET NOCOUNT ON;

        INSERT INTO CustomerDetails
        SELECT
        Customer.value('@Id','INT') AS Id, --ATTRIBUTE
        Customer.value('(Name/text())[1]','VARCHAR(100)') AS Name, --TAG
        Customer.value('(Country/text())[1]','VARCHAR(100)') AS Country --TAG
        FROM
        @xml.nodes('/Customers/Customer')AS TEMPTABLE(Customer)
END

demonstrates how to retrieve atributes from xml

Notice that you can return whole saved object from stored procedure and deserialize it in C#, also you can pass as complex object as you wish.

In this article there is sample of passing lists into stored procedure.

In our case it can be something like this:

DECLARE @post XML
SET @post = '<Post xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<PostId>4</PostId>
<Title>1111Stored Procedure XML Parameter</Title>
<AuthorId>1</AuthorId>
<Categories>
    <string>IT</string>
    <string>Accounting</string>
</Categories>
</Post>'

SELECT Category.value('.', 'NVARCHAR(100)') AS Category
FROM @post.nodes('/Post/Categories/string/text()') AS TEMPTABLE(Category)

Which will return:

Category
--------
IT
Accounting

This can be used to update multiple entites in database in one call.

SQL validate XML with XSD

Found at http://blog.sqlauthority.com/2009/12/02/sql-server-validate-an-xml-document-in-tsql-using-xsd-by-jacob-sebastian/

xsd example describes what it is and provides sample of xsd

Here is SQL to drop create XML schema collection:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'ItemsSchema') DROP XML SCHEMA COLLECTION ItemsSchema
GO

CREATE XML SCHEMA COLLECTION ItemsSchema
    AS'<?xml version="1.0" encoding="utf-8"?>
<!-- describing schema for sample xml -->
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xs:element name="items">
        <!-- root element will be <items> -->
        <xs:complexType>
            <!-- it will be complex (there are complex and simple) -->
            <xs:sequence>
                <!-- it will consists from sequence of other elements -->
                <xs:element name="item" minOccurs="0" maxOccurs="unbounded">
                    <!-- there will be zero or more <item> elements in sequence -->
                    <xs:complexType>
                        <!-- they all have complex type -->
                        <xs:sequence>
                            <!-- each of them will have next sequence of elements -->
                            <xs:element name="name" type="NonEmptyString" minOccurs="1"/>
                            <!-- required (minOccurs="1") non empty (look for NonEmptyString at bottom) name field -->
                            <xs:element name="photo" minOccurs="1">
                                <!-- required photo with given pattern to validate urls for images -->
                                <xs:simpleType>
                                    <!-- this is example how to use additional restrictions for elements -->
                                    <xs:restriction base="xs:anyURI">
                                        <xs:minLength value="1" />
                                        <xs:pattern value="http://.*(png|jpg|jpeg|gif)" />
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                            <xs:element name="tags" type="NonEmptyString" minOccurs="1"/>
                            <xs:element name="diameter" type="xs:positiveInteger" minOccurs="1"/>
                            <xs:element name="weight" type="xs:positiveInteger" minOccurs="1"/>
                            <xs:element name="price" type="positiveDecimal" minOccurs="1"/>
                            <xs:element name="size" minOccurs="0" default="">
                                <!-- example of enum field -->
                                <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                        <xs:enumeration value=""/>
                                        <xs:enumeration value="Big"/>
                                        <xs:enumeration value="Small"/>
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:simpleType name="positiveDecimal">
        <xs:restriction base="xs:decimal">
            <xs:minExclusive value="0"/>
            <xs:fractionDigits value="2"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="NonEmptyString">
        <!-- we can describe our types separately to reuse them later -->
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:pattern value=".*[^\s].*" />
        </xs:restriction>
    </xs:simpleType>
</xs:schema>'
GO

Now if you will run:

DECLARE @xml XML(ItemsSchema)
SELECT @xml = '<?xml version="1.0" encoding="utf-8"?>
<items>
    <item>
        <name>Item 1</name>
        <photo>http://example.com/photo1.png</photo>
        <tags>Tag1, Tag2</tags>
        <diameter>32</diameter>
        <weight>540</weight>
        <price>60</price>
        <size>Big</size>
    </item>
    <item>
        <name>Item 1</name>
        <photo>http://example.com/photo2.png</photo>
        <tags>Tag1</tags>
        <diameter>23</diameter>
        <weight>340</weight>
        <price>50</price>
    </item>
</items>'

All will be ok but if you git empty name, or remove name at all or give it frong photo url SQL will warn you!

For example here I trying to paste wrong photo url:

DECLARE @xml2 XML(ItemsSchema)
SELECT @xml2 = '<?xml version="1.0" encoding="utf-8"?>
<items>
    <item>
        <name>Item 1</name>
        <photo>http://example.com/photo1</photo>
        <tags>Tag1, Tag2</tags>
        <diameter>32</diameter>
        <weight>540</weight>
        <price>60</price>
        <size>Big</size>
    </item>
</items>'

Here is error:

Msg 6926, Level 16, State 1, Line 100
XML Validation: Invalid simple type value: 'http://example.com/photo1'. Location: /*:items[1]/*:item[1]/*:photo[1]

XSLT your XML right inside SQL

Here is awesomeness

-- NOTICE that class is not wrapped in namespace
/*
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Xsl;

public class XSLTTransform
{
    public static SqlXml Transform(SqlXml inputDataXML, SqlXml inputTransformXML)
    {
        MemoryStream memoryXml = new MemoryStream();
        XslCompiledTransform xslt = new XslCompiledTransform();
        XmlReader output = null;

        xslt.Load(inputTransformXML.CreateReader());

        // Output the newly constructed XML
        XmlTextWriter outputWriter = new XmlTextWriter(memoryXml, Encoding.Default);
        xslt.Transform(inputDataXML.CreateReader(), null, outputWriter, null);
        memoryXml.Seek(0, SeekOrigin.Begin);
        output = new XmlTextReader(memoryXml);

        return new SqlXml(output);
    }
}
*/


-- STEP 1. Enable CLR
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- STEP 2. reCREATE assembly and function
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ApplyXsltTransform') DROP FUNCTION ApplyXsltTransform
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'XSLTTransform') DROP ASSEMBLY XSLTTransform
GO

CREATE ASSEMBLY XSLTTransform FROM 'C:\Users\Alexandr\Documents\visual studio 2015\Projects\XSLTTransform\XSLTTransform\bin\Release\XSLTTransform.dll'
GO

CREATE FUNCTION ApplyXsltTransform(@inputXML xml, @inputTransform xml)
RETURNS XML
AS EXTERNAL NAME XSLTTransform.XSLTTransform.Transform
GO


-- STEP 3. Demo
DECLARE @xml XML = (
    SELECT TOP 10 Date, AVG(TimeTaken) AS Value
    FROM MAC.Play.dbo.IISLog
    GROUP BY Date
    ORDER BY Date
    FOR XML PATH('Item'), ROOT('ArrayOfItem')
)
SELECT @xml

--DECLARE @xslt XML = (
--  SELECT BulkColumn FROM OPENROWSET(Bulk 'C:\Users\Alexandr\Documents\visual studio 2015\Projects\XSLTTransform\XSLTTransform\Chart.xslt', SINGLE_BLOB) AS x
--)
DECLARE @xslt XML = '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html" indent="yes"/>

    <xsl:template match="/">
        <html>
            <body>
                <xsl:apply-templates/>
            </body>
        </html>
    </xsl:template>
    <xsl:template match="ArrayOfItem">
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Value</th>
                </tr>
            </thead>
            <tbody>
                <xsl:for-each select="Item">
                    <tr>
                        <td>
                            <xsl:value-of select="Date"/>
                        </td>
                        <td>
                            <xsl:value-of select="Value"/>
                        </td>
                    </tr>
                </xsl:for-each>
            </tbody>
        </table>
    </xsl:template>
</xsl:stylesheet>';
SELECT @xslt

SELECT dbo.ApplyXsltTransform(@xml, @xslt)

And its output:

<html>
    <body>
    <table>
        <thead>
        <tr><th>Date</th><th>Value</th></tr>
        </thead>
        <tbody>
        <tr><td>2015-06-11T03:00:00</td><td>14</td></tr>
        <tr><td>2015-06-11T03:00:02</td><td>182</td></tr>
        <tr><td>2015-06-11T03:00:03</td><td>149</td></tr>
        <tr><td>2015-06-11T03:00:04</td><td>43</td></tr>
        <tr><td>2015-06-11T03:00:05</td><td>12</td></tr>
        <tr><td>2015-06-11T03:00:07</td><td>181</td></tr>
        <tr><td>2015-06-11T03:00:08</td><td>144</td></tr>
        <tr><td>2015-06-11T03:00:09</td><td>141</td></tr>
        <tr><td>2015-06-11T03:00:10</td><td>524</td></tr>
        <tr><td>2015-06-11T03:00:11</td><td>2106</td></tr>
        </tbody>
    </table>
    </body>
</html>

RazorGenerator

Generator Types

• MvcHelper: Creates a static type that is best suited for writing Mvc specific helper methods.
• MvcView: Create a WebViewPage which allows the use of precompiled MVC views.
• WebPage: Creates a WebPage type that can be used as WebPages Application Part (such as _Admin and RazorDebugger).
• WebPagesHelper: Creates a HelperPage type that is suited for precompiling and distributing WebPages helper.
• Template: Generator based on T4 preprocessed template.

Usage in an MVC app

• Install the 'RazorGenerator.Mvc' package, which registers a special view engine
• Go to an MVC Razor view's property and set the Custom Tool to RazorGenerator
• Optionally specify a value for Custom Tool Namespace to specify a namespace for the generated file. The project namespace is used by default.
• Optionally specify one of the generators in the first line of your Razor file. A generator declaration line looks like this: @* Generator: MvcHelper *@. If you don't specify this, a generator is picked based on convention (e.g. files under Views are treated as MvcViews) NOTE: The selection has other criteria such as detecting "Helper" in the filename and choosing WebPagesHelper for those.
• You'll see a generated .cs file under the .cshtml file, which will be used at runtime instead of the .cshtml file
• You can also go to the nuget Package Manager Console and run Enable-RazorGenerator to enable the Custom Tool on all the views.
• And to cause all the views to be regenerated, go to the nuget Package Manager Console and run Redo-RazorGenerator. This is useful when you update the generator package and it needs to generate different code.
• ----------------------------------------------------------------

download RazorGenerator extension

in cshtml under app_code

@* Generator: MvcHelper DisableLinePragmas: true Namespace: BSS.SubProj.Web GeneratePrettyNames : true ExcludeForCodeCoverage : true*@

--helper build action none  and custom tool:RazorGenerator

--generated cs- build action compile

Suppressing Code Analysis Rules

Apply a Suppression in “Project Suppression File (GlobalSuppression.cs) – an ideal option.

Project Suppressions

When selecting the Project Supressions File option, the SuppressMessageAttribute is placed in the projects GlobalSuppressions.cs file – Visual Studio will create one automatically. The attribute has overrides which allows you to target the exact place of the rule failure such as a class, method, field, namespace etc.

In my example, I have suppressed botth CA2210 and CA1304 rules in the GlobalSuppressions.cs. Visual Studio generates the code.

// This file is used by Code Analysis to maintain SuppressMessage
// attributes that are applied to this project.
// Project-level suppressions either have no target or are given
// a specific target and scoped to a namespace, type, member, etc.
//
// To add a suppression to this file, right-click the message in the
// Error List, point to "Suppress Message(s)", and click
// "In Project Suppression File".
// You do not need to add suppressions to this file manually.

using System.Diagnostics.CodeAnalysis;

[assembly: SuppressMessage(
"Microsoft.Design",
"CA2210:AssembliesShouldHaveValidStrongNames")]

[assembly:
SuppressMessage(
"Microsoft.Globalization", "CA1304:SpecifyCultureInfo",
MessageId = "System.ServiceModel.FaultReason.#ctor(System.String)",
Scope = "member",
Target = "Wcf.Demo.Service.ErrorHandler.#ProvideFault(System.Exception,System.ServiceModel.Channels.MessageVersion,System.ServiceModel.Channels.Message&)"
)]

Notice the the CA1304 suppression contains Scope and Target properties. This can be changed so that the suppression is scoped to a wider code base such a a namespace.

The benefits of project suppressions is cleaner code, less duplication of the SuppressMessage attribute and easier to remove if failure is fixed. It’s also easier to code review as all failure overrides are in the one place. The only disadvantage that I can see is that there is an additional code file.

Always use project suppressions.

Create SSIS Environment from script and give access script

/*****************************************************************************************************
* Name              : Script
* Description       : This script is to create envirnoment variables.
* Author   : Subrat Samal
*******************************************************************************************************
* Amendment History
*------------------------------------------------------------------------------------------------------
* ID Date             User Reason
*******************************************************************************************************
* 001 07/06/2018 Subrat Samal Initial version
******************************************************************************************************/
USE [SSISDB]

--Environment Folder - Project - Env Name
DECLARE @SubProjSSISFolder NVARCHAR(128) = N'SubProjSSIS'
DECLARE @SubProjSSISProject NVARCHAR(128) = N'BSS.SubProjSSIS'
DECLARE @SubProjSSISEnv NVARCHAR(128) = N'SubProjEnv'
------------------Environment variable---------------------------
DECLARE @EnvironmentVariablesData TABLE(EnvVariableName NVARCHAR(128) NOT NULL,EnvVariableParamValue NVARCHAR(1028) NOT NULL,EnvVariableType NVARCHAR(128) NOT NULL,ProjectVariableName NVARCHAR(128) NOT NULL,EnvVariableDescription NVARCHAR(1024) NOT NULL)
INSERT INTO @EnvironmentVariablesData
  (EnvVariableName ,EnvVariableParamValue ,EnvVariableType ,ProjectVariableName ,EnvVariableDescription) 
VALUES ('up_Par1' ,N'Value1' ,'String' ,'up_Par1' ,'Desc1'),
  ('up_Par2' ,N'Value2' ,'String' ,'up_Par2' ,'Desc1'),
  ('up_Par3' ,N'Value3' ,'String' ,'up_Par3' ,'Desc1'),
  ('up_Par4' ,N'Value4' ,'String' ,'up_Par4' ,'Desc1'),
  ('up_Par5' ,N'Value5' ,'String' ,'up_Par5' ,'Desc1')
------------------ Environment variable ---------------------------

----------------- Start -- Don't  change below code ---------------
DECLARE @Folder_Id BIGINT = (SELECT folder_id FROM [SSISDB].[catalog].folders f WHERE f.name = @SubProjSSISFolder)
DECLARE @Project_Id BIGINT = (SELECT folder_id FROM [SSISDB].[catalog].projects p WHERE p.folder_id = @Folder_Id)
DECLARE @Var_Object_Type INT = 20

-- Create the Folder
IF ISNULL(@Folder_Id,0) = 0
BEGIN
EXEC [SSISDB].[catalog].[create_folder] @Folder_Name = @SubProjSSISFolder, @Folder_Id = @Folder_Id OUTPUT
END

-- Give the folder a description
EXEC [SSISDB].[catalog].[set_folder_description] @Folder_Name = @SubProjSSISFolder, @folder_description = 'Folder containing SRS-SSIS routines'

-- Environment Id
DECLARE @Environment_Id BIGINT = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE [name] = @SubProjSSISEnv  AND [folder_id] = @Folder_Id)

--Drop existing Environment
IF ISNULL(@Environment_Id,0) > 0
BEGIN
EXEC [SSISDB].[catalog].[delete_environment] @environment_name = @SubProjSSISEnv, @Folder_Name = @SubProjSSISFolder
END

-- Create the environment
EXEC [SSISDB].[catalog].[create_environment] @environment_name = @SubProjSSISEnv, @environment_description = 'Environment configuration for SRS-SSIS', @Folder_Name = @SubProjSSISFolder

--Create environment variables with the values
WHILE((SELECT COUNT(*) FROM @EnvironmentVariablesData) > 0)
BEGIN
DECLARE @EnvVariableName AS NVARCHAR(128),@EnvVariableDescription AS NVARCHAR(128),@EnvVariableValue AS NVARCHAR(1028),@EnvVariableType AS NVARCHAR(128),@PrjVariableName AS NVARCHAR(128)
SELECT TOP 1 @EnvVariableName = [EnvVariableName],@EnvVariableDescription = [EnvVariableDescription],@EnvVariableValue = [EnvVariableParamValue],@EnvVariableType = [EnvVariableType],@PrjVariableName = [ProjectVariableName] FROM @EnvironmentVariablesData

IF NOT EXISTS (SELECT * FROM [SSISDB].[catalog].[environment_variables] WHERE name = @EnvVariableName AND [Environment_id] = @Environment_Id)
BEGIN
/* Create environment variable */
EXEC [SSISDB].[catalog].[create_environment_variable]
@variable_name = @EnvVariableName, 
@sensitive = False, 
@description = @EnvVariableDescription, 
@environment_name = @SubProjSSISEnv, 
@folder_name = @SubProjSSISFolder, 
@value = @EnvVariableValue, 
@data_type = @EnvVariableType

/* Map environment variable to SSIS parameteres */
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type = @Var_Object_Type, 
@parameter_name = @PrjVariableName, 
@object_name = @SubProjSSISProject, 
@folder_name = @SubProjSSISFolder, 
@project_name = @SubProjSSISProject, 
@value_type = R, 
@parameter_value = @EnvVariableName
END

DELETE FROM @EnvironmentVariablesData WHERE [EnvVariableName] = @EnvVariableName
END
--Create environment reference -- Associate Project with the Project Parameters created above
DECLARE @reference_id BIGINT
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[environment_references] r JOIN [SSISDB].[catalog].[projects] p ON [p].[project_id] = [r].[project_id]
 WHERE [p].[name] = @SubProjSSISProject AND [r].[environment_name] = @SubProjSSISEnv AND [p].[folder_id] = @Folder_Id)
BEGIN
EXEC [SSISDB].[catalog].[create_environment_reference] 
@environment_name = @SubProjSSISEnv
,@environment_folder_name = @SubProjSSISFolder
,@reference_id = @reference_id OUTPUT
,@project_name = @SubProjSSISProject
,@Folder_Name = @SubProjSSISFolder
,@reference_type = A
END
GO
----------------- End -- Don't  change above code ---------------



---------------------------------------------------------------------------------------------------
-- Assign [SSISDB] SSIS service account permissions
---------------------------------------------------------------------------------------------------
ALTER ROLE [ssis_admin] ADD MEMBER [BSS\svc-ACCOUNT]


----------------------------------------------Acceess rights to SSIS--------------------------------------------------------
-- Fetching Environment Id to Grant Read access for SRV account
DECLARE @EnvironmentIdAccess BIGINT
SELECT
TOP 1 @EnvironmentIdAccess = Env.environment_id 
FROM
SSISDB.[catalog].environments as Env
INNER JOIN
SSISDB.[catalog].folders as Fol
ON 
Fol.folder_id =Env.folder_id
INNER JOIN 
SSISDB.[catalog].projects as Prj
ON 
Prj.folder_id = Fol.folder_id
INNER JOIN
SSISDB.[catalog].environment_references as EnvRef
ON 
EnvRef.project_id = Prj.project_id
WHERE
Env.[name] = N'SubProjEnv' AND Fol.[name] = N'SubProjSSIS'

DECLARE @PrincipalId BIGINT
SELECT @PrincipalId = DATABASE_PRINCIPAL_ID(N'BSS\svc-ACCOUNT')

EXECUTE [SSISDB].[catalog].[grant_permission] 
@object_type=3,--Default object ID for SSIS Environment 
@object_id=@EnvironmentIdAccess,
@principal_id=@PrincipalId,
@permission_type=1 --Default value for Read Permission

----------------------------------------------------------------------------------------------

--Change owner
--exec sp_changedbowner 'sa'
ALTER AUTHORIZATION ON DATABASE::[$(DatabaseName)] TO [sa]
GO

-----------------------------------------------------------------------------------------------
--execute command line

DTExec /ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" /SERVER "." /Envreference 2 /Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 /Par "Project::up_var(String)";"subrat" /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True


--------------------------------------------------------------------------
-- getting envreferecne id
--  Following PowerShell code into a .ps1 file to use it in your scripts.

Import-Module SQLServer Function Get-EnvironmentReferenceId (
[string]$EnvironmentName = "DEF", 
 [parameter(Mandatory = $true)][string]$ProjectPath) { If ($EnvironmentName -eq "DEF") 
# Get the default Environment for the current server environment { $EnvironmentName = [Environment]::GetEnvironmentVariable( 
"ENVIRONMENT_ID", "Machine") } $ProjectPathParts = $ProjectPath.Split("\") $ReferenceIdQuery = " SELECT er.reference_id FROM [internal].[folders] AS f JOIN [internal].[projects] AS p ON f.folder_id = p.folder_id JOIN [internal].[environment_references] AS er ON p.project_id = er.project_id WHERE f.name = '{0}' AND p.name = '{1}' AND er.environment_name = '{2}'" 
-f $ProjectPathParts[2], $ProjectPathParts[3], $EnvironmentName #$ReferenceIdQuery $QueryResults = Get-SqlData 
-SqlServer localhost 
-dbname SSISDB 
-qry $ReferenceIdQuery If ($QueryResults -eq $Null) { Throw New-Object System.ArgumentException 
"Environment '$EnvironmentName' is not configured for use with '$ProjectPath' on the this server.", 
"EnvironmentName" } Return $QueryResults[0] }

---------------------------------------------
Set-Location "C:\SSISScripts" CLS . .\GetEnvironmentReferenceId.ps1 # Determine which deployment model was used for this package # and set the DTExec arguments appropriately If ($PackagePath.SubString(0, 7) -eq "\SSISDB") { $EnvironmentReferenceId = Get-EnvironmentReferenceId $Environment $PackagePath $DTExecArgs = "/ISSERVER ""$PackagePath"" /Env $EnvironmentReferenceId /CHECKPOINTING OFF /REPORTING EW /Par """"`$ServerOption::SYNCHRONIZED(Boolean)"";True""" } Else { $DTExecArgs = "/DTS ""$PackagePath"" /CHECKPOINTING OFF /REPORTING EW" } Get-Location # Run DTExec $pinfo = New-Object System.Diagnostics.ProcessStartInfo If ($Architecture -eq "64") { $pinfo.FileName = "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" } Else { $pinfo.FileName = "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" } $pinfo.FileName # Output the DTExec path and filename $DTExecArgs # Output the DTExecArgs variable # The next few lines are required to make sure the process waits for # the package execution to finish $pinfo.RedirectStandardOutput = $true $pinfo.UseShellExecute = $false $pinfo.Arguments = $DTExecArgs $p = New-Object System.Diagnostics.Process $p.StartInfo = $pinfo $p.Start() | Out-Null $output = $p.StandardOutput.ReadToEnd() $p.WaitForExit() $DTExecExitCode = $p.ExitCode $output # DTExec Finished # This If/Else block is for our third-party scheduler that thinks # that negative return codes are equivalent to "success" If ($DTExecExitCode -ge 0) { $Result = $DTExecExitCode } Else { $Result = 0 - $DTExecExitCode } Write-Output "Return Code = $Result" Exit $Result