Category Archives: XML, XQuery and XPath

Modify element value for typed and untyped xml

First, let’s define an untyped xml and modify it:   declare @x xml SET @x=N‘ <Properties xmlns="http://www.xyz.com/p01"&gt;       <Version>1.0.0</Version>       <MyProperties>       <Property>P1</Property>       </MyProperties> </Properties>’ /* — comment 1 SET @x.modify(‘ declare namespace p="http://www.xyz.com/p01&quot;; replace value of (/p:Properties/p:Version/text())[1] with  … Continue reading

Posted in XML, XQuery and XPath | 2 Comments

Output xml and relational data in OPENXML

This question arose from a post in SQL server forum. Due to performance reason, it is required to use OPENXML to process a big xml documnet. But it also needs to return a specific xml document for each record.The source … Continue reading

Posted in XML, XQuery and XPath | Leave a comment

Request full support for position() function in xquery

/* Right now position() can only be used in predict in xquery. However, in lots of scenarios, the position of the xml data is significant. */ DECLARE @x xml; SET @x = N‘<SelectAnswers xmlns="http://MyNamespace"&gt; <AnswerType Type="selectOne" IsRequired="true"> <SelectAnswer>Once a month</SelectAnswer> … Continue reading

Posted in XML, XQuery and XPath | Leave a comment

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