XML has become an easiest mode to exchange and transfer data. There can be a scenario when we have received data in xml format and we want to store in tabular format in one of the table that we have created in SQL Server. Below is how we can go about solving such a problem.
Say we have an XML fragment as below:
<ProductDetails>
<Products>
<ProductCategoryID>3</ProductCategoryID>
<Name>Mountain-100 Red, 38</Name>
<Weight>9230.56</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Red</Color>
<Size>38</Size>
</Products>
<Products>
<ProductCategoryID>4</ProductCategoryID>
<Name>Mountain-100 Blue, 42</Name>
<Weight>9421.06</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>blue</Color>
<Size>42</Size>
</Products>
<Products>
<ProductCategoryID>5</ProductCategoryID>
<Name>Mountain-100 Silver, 44</Name>
<Weight>9584.36</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Silver</Color>
<Size>44</Size>
</Products>
<Products>
<ProductCategoryID>6</ProductCategoryID>
<Name>Mountain-100 Orange, 48</Name>
<Weight>9715.90</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Orange</Color>
<Size>48</Size>
</Products></ProductDetails>
We will first initialize it in a variable like so
Declare @XML as XML
set @XML='<ProductDetails>
<Products>
<ProductCategoryID>3</ProductCategoryID>
<Name>Mountain-100 Red, 38</Name>
<Weight>9230.56</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Red</Color>
<Size>38</Size>
</Products>
<Products>
<ProductCategoryID>4</ProductCategoryID>
<Name>Mountain-100 Blue, 42</Name>
<Weight>9421.06</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>blue</Color>
<Size>42</Size>
</Products>
<Products>
<ProductCategoryID>5</ProductCategoryID>
<Name>Mountain-100 Silver, 44</Name>
<Weight>9584.36</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Silver</Color>
<Size>44</Size>
</Products>
<Products>
<ProductCategoryID>6</ProductCategoryID>
<Name>Mountain-100 Orange, 48</Name>
<Weight>9715.90</Weight>
<Date>1998-06-01T00:00:00</Date>
<Color>Orange</Color>
<Size>48</Size>
</Products></ProductDetails>'
--In this we will use query method to retrieve the records
select P.Products.query('./ProductCategoryID').value('.','int') AS ProductCategoryID,
P.Products.query('./Name').value('.','varchar(50)') AS ProductName,
P.Products.query('./Weight').value('.','float') AS ProductWeight,
P.Products.query('./Date').value('.','Date') As Date,
P.Products.query('./Color').value('.','varchar(30)') ProductColor,
P.Products.query('./Size').value('.','int') as ProductSize
from @XML.nodes('ProductDetails/Products') as P(Products);
Result:
