XML to SQL and Update the SQL table using the same XML

//This below post will depict how you can insert the XML in the SQL table columns, how do you update the values of the Table reading the same XML

--Script for unified locations

DECLARE @LocationXml XML
--Assume that this is the Input XML.
SET @LocationXml = '
                        New York
                        Sydney
                        Europe
                    '

--Assume this is the existing Datbase with locations
DECLARE @Exbname TABLE(
cLocations nvarchar(max),
cCountryName nvarchar(MAX),
cID int
)

--Assume this is the existing Location inserted in the DB
INSERT INTO @Exbname VALUES('a','c',0)


--Assume this is the new Locations given by the supplier in the same format.

DECLARE @tbname TABLE(
cLocations nvarchar(max),
cCountryName nvarchar(MAX),
cID int
)

--Assume this is the new Locations cached in a temp table. (so that we decied to either insert or update them)

INSERT INTO @tbname
SELECT  LocationXml.snx.value('(.)[1]', 'NVARCHAR(50)') AS 'cLocations',
        LocationXml.snx.value('(./@Country)[1]', 'NVARCHAR(50)') AS 'cCountryName',
        LocationXml.snx.value('(./@ID)[1]', 'NVARCHAR(50)') AS 'cID'
FROM    @LocationXml.nodes('//Locations/Location') LocationXml ( snx )   


--SELECT * FROM @tbname --WHERE [@tbname].cID = 3


--This is how we will update the existing loations
UPDATE extb
SET  extb.cLocations = newtb.cLocations,
extb.cCountryName = newtb.cCountryName
FROM @tbname newtb INNER JOIN @Exbname extb ON newtb.cID = extb.cID

-- This is how we insert the new locations

INSERT INTO @Exbname
SELECT * FROM @tbname WHERE cID NOT IN (
SELECT cID FROM @Exbname
)

SELECT * FROM @Exbname

Removing unwanted nodes from XML, keeping the XML hierarchy same

Removing unwanted nodes from XML, keep the XML hierarchy same

Lets say we have  XML as -
<Hotels>
    <Rooms>
        <name>Standars</name>
        <Type>Deluxe</Type>
        <Rate>123123</Rate>
    <Amenities>sfds</Amenities>
    <Qty>2</Qty>
    </Rooms>
    <Rooms>
        <name>Delux</name>
        <Type>Superior</Type>
        <Rate>453</Rate>
    <Amenities>234</Amenities>
    <Qty>5</Qty>
    </Rooms>
    <Rooms>
        <name>Suite</name>
        <Type>Superior</Type>
        <Rate>3242</Rate>
    <Amenities>xdg</Amenities>
    <Qty>7</Qty>
    </Rooms>
</Hotels>

and we want output as -


<Hotels>
    <Rooms>
        <name>Standars</name>
        <Type>Deluxe</Type>
    </Rooms>
    <Rooms>
        <name>Delux</name>
        <Type>Superior</Type>
    </Rooms>
    <Rooms>
        <name>Suite</name>
        <Type>Superior</Type>
    </Rooms>
</Hotels>      



Below is a linq that can help us to get above result.



var document = XDocument.Load("XMLFile1.xml");
        List<string> removeNames = new List<string> { "Rate", "Amenities", "Qty"}; //Collection of fields to be removed.
        var removeItems = from item in document.Descendants("HotelSummary")
                          let ritems = item.Elements().Where(i => removeNames.IndexOf(i.Name.ToString()) >= 0)
                          select ritems;
      
        foreach (var item in removeItems)
        {
            item.Remove();
        }
        document.Save("d:\\try.xml");

Sorting XML using Linq on Attribute

//Lets say you want sort an XML on some intiger value something as below.
string mySchedules = "<showschedule>" +
"<fall name=\"FAL\" text=\"Y\" index=\"3\"/>" +
"<spring name=\"SPR\" text=\"Y\" index=\"2\"/>" +
"<winter name=\"WIN\" text=\"Y\" index=\"1\"/>" +
"<summer name=\"SUM\" text=\"Y\" index=\"4\"/>" +
"</showschedule>";

XElement xShows = XElement.Parse(mySchedules);

//This is how you sort
IEnumerable<XElement> sortShows = from s in xShows.Descendants()
orderby (int)s.Attribute("index")
select s;

//This will give you output in the object sortShows as below.

/*
"<showschedule>" +
"<winter name=\"WIN\" text=\"Y\" index=\"1\"/>" +
"<spring name=\"SPR\" text=\"Y\" index=\"2\"/>" +
"<fall name=\"FAL\" text=\"Y\" index=\"3\"/>" +
"<summer name=\"SUM\" text=\"Y\" index=\"4\"/>" +
"</showschedule>";

*/

//You can see the output in the below String
string commaSeperatedSortedMsg = "";
foreach (XElement xE in sortShows)
{
commaSeperatedSortedMsg += " , " + xE.Attribute("index").Value + xE.Attribute("name").Value;

}

Avoiding xmlns="" while appending new XML node

To avoid xmlns="" attibute which gets added when you append a new node in to your existing XML document.

The main point is that the namespace of a node is determined when you create it, not when you insert it.

So when you create your new nodes you need to make sure you create them under a proper namespace.
If you already have a node and that is in no namespace and you insert it somewhere into a node that is in a namespace then
the serializer has to add the xmlns="" to ensure the node remains in no namespace.
That is how the DOM works, there is no way to change that.

So as already suggested, make sure you create nodes in the namespace you want them in.

Check here the detail how I have used below line

XNamespace xN = "http://www.sitemaps.org/schemas/sitemap/0.9";


Try this below working code, to run the sample.

XDocument xDoc = XDocument.Load(Server.MapPath("sitemap.xml"));
XNamespace xN = "http://www.sitemaps.org/schemas/sitemap/0.9";


XElement xUrlNode = new XElement(xN + "url");
XElement xLocNode = new XElement(xN + "loc","my Location");
XElement xlastmod = new XElement(xN + "lastmod", "new()");
XElement xchangefreq = new XElement(xN + "changefreq", "weekly");
XElement xpriority = new XElement(xN + "priority", "0.8");


//Lets insert the nodes in reverse case
xUrlNode.Add(xLocNode);

//Add the child node in the URL node
xUrlNode.Add(xlastmod);
xUrlNode.Add(xchangefreq);
xUrlNode.Add(xpriority);

//Add the URL Node
xDoc.Root.Add(xUrlNode);

string seeOutPutHere = xDoc.ToString();

xDoc.Save(Server.MapPath("sitemap.xml"));


This was my sitemap.xml input before I ran the code.

<?xml version="1.0" encoding="utf-8"?&>t;
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd"&>t;
<url&>t;
<loc&>t;http://www.eureferendum.com/blogview.aspx?blogno=11</loc&>t;
<lastmod&>t;2012-03-21</lastmod&>t;
<changefreq&>t;weekly</changefreq&>t;
<priority&>t;0.8</priority&>t;
</url&>t;
</urlset&>t;


This will give you output as below.

<?xml version="1.0" encoding="utf-8"?&>t;
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd"&>t;
<url&>t;
<loc&>t;http://www.eureferendum.com/blogview.aspx?blogno=11</loc&>t;
<lastmod&>t;2012-03-21</lastmod&>t;
<changefreq&>t;weekly</changefreq&>t;
<priority&>t;0.8</priority&>t;
</url&>t;
<url&>t;
<loc&>t;my Location</loc&>t;
<lastmod&>t;new()</lastmod&>t;
<changefreq&>t;weekly</changefreq&>t;
<priority&>t;0.8</priority&>t;
</url&>t;
</urlset&>t;

SQL table to a text file and an XML file

Writing records from SQL table to a text file and an XML file using C#.net

string conStr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
string destinationFile= "C:\\CreateMe.txt";

FileStream stream = new FileStream(physicalPath, FileMode.Open, FileAccess.ReadWrite);
StreamWriter writer = new StreamWriter(stream);
SqlConnection con = new SqlConnection();
con.ConnectionString = conStr;
SqlCommand com = new SqlCommand("Select Name,ID FROM [User]", con);
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
writer.Write(reader["Name"].ToString());
writer.Write(",");
writer.Write(reader["ID"].ToString());
writer.WriteLine();
}
reader.Close(); writer.Close(); stream.Close();

Reading XML Attribute, directly in SQL.

DECLARE @statXML xml;

SET @statXML =
 N'<statistics>

   <element code="P10" type="   range" />
   <element code="P25" type="   range" />
   <element code="P50" type="   range" />
   <element code="P75" type="   range" />
   <element code="P90" type="   range" />
 </statistics>
';

SELECT
t.c.value(N'@code', N'nvarchar(10)') AS code,
t.c.value(N'@type', N'nvarchar(10)') AS code
FROM
@statXML.nodes(N'/statistics/element') t(c);

This should give you output as


Code Type

---

P10    range
P25    range
P50    range
P75    range
P90    range

XML to HTML using XSLT

Check out the most simple Example using which you can convert your XML to HTML. For this transition if you will use the XSLT, it will be really easy for you.

Assume that following is the XML from which you want to read data and convert it to HTML.

inputxml.xml

<Persons>   <Person image="http://someIMageURL">     <Name>Kavita  </Name>     <Phone>123123  </Phone>   </Person>   <Person image="http://someIMageURL">     <Name>Kavita1  </Name>     <Phone>456  </Phone>   </Person>   <Person image="http://someIMageURL">     <Name>Kavita2  </Name>     <Phone>23423  </Phone>   </Person>   <Person image="http://someIMageURL">     <Name>Kavita3  </Name>     <Phone>99090  </Phone>   </Person>   </Persons>

Now if you write XSLT as below.

<?xml version="1.0"?>    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">    <xsl:output doctype-public="HTML"/>    <xsl:template match="Persons">    <table border="1">     <tr>      <td>Sr.no   </td>      <td>Name   </td>      <td>Phone   </td>      <td>Image   </td>    </tr>    <xsl:for-each select="Person">    <tr>    <td>    <xsl:value-of select="position()"/>    </td>    <td>    <xsl:value-of select="Name"/>    </td>    <td>    <xsl:value-of select="Phone"/>    </td>    <td>    <xsl:value-of select="@image"/>    </td>    </tr>    </xsl:for-each>    </table>    </xsl:template>    </xsl:stylesheet>

And apply it in the above mentioned XML then you will get an HTML output as following

Sr.no	Name	Phone	Image
1	Kavita	123123	http://someIMageURL
2	Kavita1	456	http://someIMageURL
3	Kavita2	23423	http://someIMageURL
4	Kavita3	99090	http://someIMageURL