Generating XML through records in MSSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generating XML through records in MSSQL

Author

Topic

uroosa
Starting Member

1 Post

Posted - 2012-08-26 : 06:32:47

Hi,

I have these records

ProductName--DimensionCatgeory--DiemensionName--DimensionValue

StylishShoes Men Color Black
StylishShoes Men Color Beige
StylishShoes Men Color Gold

and I have to generate XML like this

<Records>
<Record>
<PROP NAME="PRODUCTNAME">
<PVAL>StylishShoes</PVAL>
</PROP>
<PROP NAME="DIMENSIONCATEGORY">
<PVAL>Men<PVAL>
</PROP>
<PROP NAME="DIMENSIONNAME">
<PVAL>Color<PVAL>
</PROP>
<PROP NAME="DIMENSIONVALUE">
<PVAL>Black<PVAL>
</PROP>
<PROP NAME="DIMENSIONVALUE">
<PVAL>Beige<PVAL>
</PROP>
<PROP NAME="DIMENSIONVALUE">
<PVAL>Gold<PVAL>
</PROP>
</Record>
</Records>

Please Hep its Urgent...I am new to this

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-26 : 10:06:29

I have couple of concerns about the XML you posted.

First, it is not well-formed. I am assuming that is just typo. I have fixed the typos in the following example.

Even bigger concern is that the nature of problem calls for a hierarchical data structure i.e., StylishShoes->Men->Colors->ColorNames. Yet, your data is a flat XML i.e., all the nodes are at the same level. The one big advantage of XML is that such hierarchical data can be represented very well in XML, but your data is not taking advantage of that feature.

The fact that the data is flat should not have been a problem, except that the XQuery in SQL is very limited. Although W3C specifications allow navigations to sibling axes, there is no next-sibling/previous-sibling type of queries in SQL XML. That makes it very hard to shred the data in the way you want it in all except trivial cases.

I am assuming that the example you posted is a trivial example - that you have many more ProductNames, DimensionCategories and DimensionNames. If that is not the case, - if this is your entire universe of types, then the query can be like this:

CREATE TABLE #tmp (x XML);
DECLARE @x XML = '<Records>
  <Record>
    <PROP NAME="PRODUCTNAME">
      <PVAL>StylishShoes</PVAL>
    </PROP>
    <PROP NAME="DIMENSIONCATEGORY">
      <PVAL>Men</PVAL>
    </PROP>
    <PROP NAME="DIMENSIONNAME">
      <PVAL>Color</PVAL>
    </PROP>
    <PROP NAME="DIMENSIONVALUE">
      <PVAL>Black</PVAL>
    </PROP>
    <PROP NAME="DIMENSIONVALUE">
      <PVAL>Beige</PVAL>
    </PROP>
    <PROP NAME="DIMENSIONVALUE">
      <PVAL>Gold</PVAL>
    </PROP>
  </Record>
</Records>';

INSERT INTO #tmp VALUES (@x);

SELECT
	c2.value('.[@NAME="PRODUCTNAME"]','varchar(32)'),
	c3.value('.[@NAME="DIMENSIONCATEGORY"]','varchar(32)'),
	c4.value('.[@NAME="DIMENSIONVALUE"]','varchar(32)')
FROM
	YourTable t
	CROSS APPLY x.nodes('/Records/Record/PROP') T2(c2)
	CROSS APPLY x.nodes('/Records/Record/PROP') T3(c3)
	CROSS APPLY x.nodes('/Records/Record/PROP') T4(c4)
WHERE
	c2.value('.[@NAME="PRODUCTNAME"]','varchar(32)') IS NOT NULL 
	AND c3.value('.[@NAME="DIMENSIONCATEGORY"]','varchar(32)') IS NOT NULL 
	AND c4.value('.[@NAME="DIMENSIONVALUE"]','varchar(32)') IS NOT NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 14:30:54

[code]
declare @XMLdata table
(
ProductName varchar(100),
DimensionCatgeory varchar(100),
DimensionName varchar(100),
DimensionValue varchar(100)
)

insert @XMLdata
values('StylishShoes', 'Men', 'Color', 'Black'),
('StylishShoes', 'Men', 'Color', 'Beige'),
('StylishShoes', 'Men' ,'Color', 'Gold')

;With CTE
AS
(
select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SEq,ROW_NUMBER() OVER (PARTITION BY PVal,Name ORDER BY (SELECT 1)) AS GSEq,*
from(
select distinct ProductName,DimensionCatgeory,DimensionName
from @XMLdata
)m
cross apply (select dimensionvalue
from @XMLdata
where ProductName = m.ProductName
and DimensionCatgeory = m.DimensionCatgeory
and DimensionName = m.DimensionName
)n
unpivot(PVal FOR Name IN ([ProductName],[DimensionCatgeory],[DimensionName],[dimensionvalue]))u
)
select Name AS '@Name',
PVal AS 'PVal'
from (select * from CTE WHERE GSEQ=1) c
order by seq
for xml path('PROP'),root('Record')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources