Converting XML document to tabular format in SQL Server

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:

Tags: , , , ,

SQL Server | SQL Tricks | XML