Transformation of XML tree to flat text using SQL Server 2008 XQuery?
"YOU AND THE ART OF ONLINE DATING" is the only product on the market that will take you step-by-step through the process of online dating, provide you with the resources to help ensure success. Get it now!
Not pretty but it seems to work. A recursive UDF create function GetExpression(@expr xml) returns varchar(max) as begin declare @max int declare @i int = 1 declare @nodetype varchar(50) declare @aggregator char(1) declare @res varchar(max) = '(' declare @value varchar(36) declare @SubExpr xml select @max=count(*) from @expr. Nodes('/expression/*') as n(e) select @aggregator = n.e.
Value('@aggregator', 'char(1)') from @expr. Nodes('expression') as n(e) while @i Value('. ', 'varchar(36)'), @SubExpr = x.
Query('.') from @expr. Nodes('/expression/*position()=sql:variable("@i")') e(x) if @nodetype = 'indicator' set @res = @res + '' + @value + '' else if @nodetype = 'expression' set @res = @res + dbo. GetExpression(@SubExpr) else if @nodetype = 'value' set @res = @res + @value if @i.
Not pretty but it seems to work. A recursive UDF. Create function GetExpression(@expr xml) returns varchar(max) as begin declare @max int declare @i int = 1 declare @nodetype varchar(50) declare @aggregator char(1) declare @res varchar(max) = '(' declare @value varchar(36) declare @SubExpr xml select @max=count(*) from @expr.
Nodes('/expression/*') as n(e) select @aggregator = n.e. Value('@aggregator', 'char(1)') from @expr. Nodes('expression') as n(e) while @i Value('local-name(.)1', 'varchar(36)'), @value = x.
Value('. ', 'varchar(36)'), @SubExpr = x. Query('.
') from @expr. Nodes('/expression/*position()=sql:variable("@i")') e(x) if @nodetype = 'indicator' set @res = @res + '' + @value + '' else if @nodetype = 'expression' set @res = @res + dbo. GetExpression(@SubExpr) else if @nodetype = 'value' set @res = @res + @value if @i.
Thanks a lot - not very pretty indeed, but I guess the pretty variants don't work because of the SQL Server XQuery limitations... ;) I'll try it out with my test cases (from reading the code I think that it will work fine). – Lucero Apr 20 at 23:14.
Mikael, you got me on the right track, here's my final solution: CREATE FUNCTION dbo. GetExpression (@expr xml) RETURNS varchar(max) AS BEGIN RETURN STUFF( ( SELECT a.x. Value('.', 'char'), CASE WHEN v.x.
Exist('self::expression')=1 THEN '('+dbo. GetExpression(v.x. Query('.
'))+')' WHEN v.x. Exist('self::indicator')=1 THEN ''+REPLACE(v.x. Value('.
', 'varchar(35)'), '-', '')+'' ELSE v.x. Value('.', 'varchar(20)') END FROM @expr. Nodes('expression/@aggregator') a(x) CROSS APPLY @expr.
Nodes('expression/*') v(x) FOR XML PATH('') ), 1, 1, ''); END.
Much nicer :). It should also be possible with a recursive CTE but I think you have to use the old openxml and use @mp:id and @mp:parentid to build the hierarchy. I don't know if there are any equivalent functions when using the XML data type.
– Mikael Eriksson Apr 21 at 6:41 Yeah, I also though about doing one with a CTE which always converts the expressions without nested expressions to the textual representation until there aren't any tags in it anymore, but it didn't really work either. I think the UDF is a good compromise (and maybe even the more efficient one). – Lucero Apr 21 at 9:13.
Easy to use XML editor for authoring and editing XML documents. Visual XML Writer is a industry leading-edge solution for XML-based software development. Visual-XML 1.1 is a development tool for rapidly creating XML-based business-to business solutions.
Visual editor for generating XML content and re-usable models. W2XML 2.0 is DocSoft's first full release to the extremely popular Word-To-XML 1.0 software. DocSoft's W2XML v2.1 software is a Word to XML conversion software.
It converts DOC, RTF, HTM and more to well-formed XML. Stilo WebWriter is an XML Document Processor designed specifically for quick, easy, effortless keying of XML conformant data. Native support for SGML in a special version of the mainstream word processor includes real-time validation against arbitrary DTDs.
A full-featured, validating SGML-only editor with a configurable user interface and a full API. Java Swing-based XML editor analyzes XML Schema and uses it to generate document-specific GUI. An XML text editor, XED is intended for technical users who want to hand-edit XML documents.
Use a simple graphical interface to define business rules to validate the correctness, completeness and consistency of core business data. XMetaL Author 5.0 is JustSystems’ new XML-based authoring and content collaboration software. XMetaL allows anyone who is familiar with word processing to create, edit and contribute valid XML documents and data.
XML Editor for GUI based editing of XML. Tree, modifications are applied using controls. Easy and simple, capable of displaying multiple views.
Small sets of XML-based data." This editor is the first with a data-centric (vs. document-centric) view of what XML is about. XML Quik Builder generates dynamic Forms to create/edit XML based on XSD.
No Knowlege of XML Required to create/edit XML. Kleinschmidt provides in-internet translation of data to/from XML. The fast, easy to use XML editor for Windows developers.
XmlBlueprint makes editing XML faster, easier, and more enjoyable. The fast, easy to use XML editor for Windows developers. XmlBlueprint makes editing XML faster, easier, and more enjoyable.
XMLdocs is a web-based content authoring, management and publishing system that is radically different from existing XML content solutions. XMLEditPro v2.0 is a powerful, small and simple xml editor. Developing in C++ for fast performance.
XmlHack is the ultimate text editor specialized for XML. XmlLinguist is a bidirectional, Text-to-XML translator. XMLMax is an easy to use and very fast XML editor for Windows.
An XML editor for data oriented documents. XMLRover is the No.1 XML testing tools. XmlShell, the ultimate lightweight XML editor, is a Windows-based XML editor.
It's fast, powerful, flexible, and intuitive. XMLSpear is a graphical tree editor, targeted to check, and correct XML files in a simple way. You can assign a schema or DTD to validate the xml.
XmlTool is a multi-purpose tool for XML files. You use mouse and drag & drop to copy, paste, move the nodes on the tree. XmlTree is a visual parser for XML files.
Is developed using ONLY visual basic (no external libraries or MSIE5 is required). Xopus is a browser based wysiwyg xml editor. Xopus offers a friendly user interface to edit complex xml content.
XPontus is a free java based XML editor designed towards text editing. Xselerator is a fully featured XSL IDE (Integrated Development Environment). XSLfast is the world's first graphical editor for XSL:FO documents.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
Querying an XML list in sql server using Xquery?
How do I add a node or new element in SQL Server using XQuery and the xml datatype?
How to write a xquery in SQL Server to read all the childnodes from an XML data column in a table?
Why is SQL Server Compact Edition missing from the list of server types in SQL Server Management Studio 2008?
Using a variable in a SQL Server 2005 stored procedure with XQuery?
OpenGL revert all transformation done by a transformation matrix?