Monthly Archives: August 2008

SQLCMD with XML ON to output xml type column to file

/* Suppose we need to export the data of an xml column from a table by TSQL, e.g. a batch or a store procedure. The xml length may exceed 8KB. We can use sqlcmd together with :XML ON command.(See this … Continue reading

Posted in XML, XQuery and XPath | 1 Comment

Convert elements to attributes for XML

/* Suppose you have the following element centric xml document. You want to change it to attribute centric. How can we do it by SQL Server xquery? I didn’t find a straight forward way to achieve this by xquery. The … Continue reading

Posted in XML, XQuery and XPath | Leave a comment

Create XML for recursive data — Part 2

/* Now Let’s test the stored procedure. We need to create the temp table #Nodes, which defines the element name for each level. If -1 is defined, any levels not defined will take the element name with LevelID=-1. The table … Continue reading

Posted in XML, XQuery and XPath | Leave a comment

Create XML for recursive data — Part 1

For recursive data, create an XML to represent its structure is challeging in SQL Server. The folowing stored procedure can generate the xml according to the levels of the hirarchy, the element name for each level and attributes to be … Continue reading

Posted in XML, XQuery and XPath | Leave a comment