XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
+ Stylus Studio Technical Forum (14621)
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
+ XQuery Help and Discussion (2016)
+ Stylus Studio FAQs (159)
- Stylus Studio Code Samples & Utilities (364)
-> + XSD to XML Creation with all O... (2)
-> - How XML to X12 be converted an... (1)
-> - Encode and decode XML to and f... (1)
-> + Evaluating StylusStudio - .cnv... (2)
-> - Build safe NCName (1)
-> - generating .Net dataset (1)
-> + Converters for .NET (2)
-> + TRADACOMS - Code Generation (2)
-> - Flat File Custom XML conversio... (1)
-> - Need help in Generate a checkb... (1)
-> + Evaluation period for the buil... (2)
-> + Reports with ifs (2)
-> + Need Help Creating Custom XML ... (6)
-> + Need help with unique particle... (10)
-> + In need for help on converting... (3)
-> - import xml into an access (1)
-> + jaxb support (3)
-> + Large XML sample file (2)
-> + Stylus Studio 2008 R2 - From X... (2)
-> + create an xml document with ex... (2)
-> + Default xml converter from com... (2)
-> + Transforming Data With XSLT (5)
-> + Error when creating pdf file (5)
-> + Got Shkespear XML Doc from web... (3)
-> + VFP 8 (2)
-> + CSV import (7)
-> + Generating X12 from XML (4)
-> + How to handle variable input &... (2)
-> + Grab html to xml (2)
-> + XML To X12 conversion using in... (3)
-> + XSLT mapping help required (2)
-> + convert XML to JAVA (2)
-> + Insert a carriage return or li... (3)
-> + Automatation for comparing two... (2)
-> - Filter records (1)
-> + BIT Fields (3)
-> + Restricted Mixed Content (4)
-> - Freelance Project - Mapping di... (1)
-> + flat file to xml conversion (10)
-> + ConvertFromXML to TXT (2)
-> + Edi X12 835 to Flat file (2)
-> + License problem (2)
-> + Removal of elements from an XM... (3)
-> + XSL and Word docs (2)
-> + Develop a workflow using xml (2)
-> + Need help converting CSV to Ne... (2)
-> + Specify valid values for an el... (3)
-> + Question about the WYSIWYG fun... (2)
-> + XML diff tool (4)
-> + Validating schemas (3)
-> + Best way to fix a few hundred ... (6)
-> + Mapping problem please help me (2)
-> + Relational Database Hierarchic... (2)
-> + RDBMS - XML round-tripping (3)
-> + Converto FILE to XML (2)
-> - relational database acces - lo... (1)
-> + unsupportet URL scheme when tr... (6)
-> + XML in oil industry (3)
-> - Want to get the available para... (1)
-> + XSL in HTML? (9)
-> + Help Creating a new wsdl (2)
-> + Extracting XML from a database... (6)
-> + Stylus Studio Converters comma... (18)
-> - Schema for RDF section within ... (1)
-> + Text to XML converter not in t... (4)
-> + Filtering data with a combo bo... (2)
-> + Loading data in XML file/docum... (2)
-> + Stylus StudioŽ's Web Service C... (2)
-> - How to replace a specific node... (1)
-> + xsl:result-document and multip... (2)
-> - Russian Doll Schema (1)
-> + Populating MS access from XML ... (9)
-> + xsd to xml (5)
-> + populating treeview control wi... (6)
-> + Problem generating a simple DT... (4)
-> + XML Newbie Basics (4)
-> + How do I ... Build an HTML Pag... (2)
-> + .NET API (6)
-> - Building Your Own Search Engin... (1)
-> - transformer.transform gives Ex... (1)
-> + reading xml (6)
-> + How to Display Links in XML (4)
-> - how to place the text of the f... (1)
-> + Need guidelines (3)
-> + Basic XML question (2)
-> + sql/xml returns invalid result... (2)
-> + SQL/XML Error (4)
-> - pass paramter in java and do a... (1)
-> + serialize java (2)
-> + pass paramter in java for xml ... (2)
-> + transform dtd to xml schema (2)
-> + Converter - xsd to xml (7)
-> + Convert to XML programmaticall... (2)
-> + XSL FO with Java data source (2)
-> - OASIS XML Catalogs Tutorial (1)
-> + Transforming XML to EDI (2)
-> - Using row data to populate XML... (7)
-> ->Using row data to populat...
-> ->Using row data to populat...
-> ->Using row data to populat...
-> ->Using row data to populat...
-> ->Using row data to populat...
-> ->Using row data to populat...
-> + Nesting Tables and Sub-Tables (2)
-> + XSL for multiple columns and c... (5)
-> + New Advanced CSV to XML Data C... (3)
-> - Java mapper (1)
-> - XML Spy project conversion to ... (1)
-> + APIs and OCXs (2)
-> + No Topic (2)
-> + Profiling (3)
-> + xsl to xslfo (5)
-> + Does anyone use XML from ADO ... (5)
-> - XML from ADO Wizard (1)
-> + Weird XQuery results? (3)
-> - XSV xml schema validator (1)
-> + Menu structure (2)
-> + XQuery user-defined functions (2)
-> + Passing Parameter to Templates (3)
-> + XML To Database (2)
-> + XML Schema xsd:any and xsd:any... (2)
-> - XSL-FO Webinar Sample Files (1)
-> + Name Element (2)
-> + date of the day (5)
-> + Is there a 'Find Definition' f... (2)
-> + DTD to schema conversion (2)
-> + Simple XSLFO Example (3)
-> - Post your favorite Stylesheet ... (1)
-- [1-20] [21-40] [41-60] Next
+ Stylus Studio Announcements (113)
Topic  
Postnext
Jim ByrneSubject: Using row data to populate XMLELEMENT name
Author: Jim Byrne
Date: 25 Jan 2005 11:40 AM
I am trying to create an XMLElement name from the row data
within a SQL Server 2000 table or view? Can I use CONCAT, XMLAgg,
or is there another way to do this within DB to XML?

Postnext
Ivan PedruzziSubject: Using row data to populate XMLELEMENT name
Author: Ivan Pedruzzi
Date: 25 Jan 2005 02:17 PM
Hi Jim,
Use XMLCONCAT to concatenate 2 columns to one XML element.
Here is an example based on the "pub" database

SELECT
XMLELEMENT(name "row",
XMLCONCAT(t.title_id, '----',t.title)
)
FROM pubs.dbo.titles t

Hope this helps
Ivan


Postnext
Jim ByrneSubject: Using row data to populate XMLELEMENT name
Author: Jim Byrne
Date: 25 Jan 2005 02:55 PM
Hey Ivan,
I should have been a bit more precise. I need to derive the tag name if
possible?

SELECT
XMLELEMENT(name XMLCONCAT('"', t.title,'"'),t.comment)
FROM pubs.dbo.titles t

Assuming the t.title field value "Apple", and t.comment of "Hello".
XML produced:

<Apple>Hello</Apple>

Thanks again.

Postnext
Ivan PedruzziSubject: Using row data to populate XMLELEMENT name
Author: Ivan Pedruzzi
Date: 25 Jan 2005 05:40 PM
Jim

SQL/XML defines 3 functions for shaping XML, below you can see a query that use them

SELECT
XMLELEMENT(name "row",
XMLATTRIBUTES(t.title_id as "id"),
XMLELEMENT(name "title", t.title),
XMLFOREST(t.price, t.royalty)
)
FROM pubs.dbo.titles t


By design SQL/XML does not allow to create elements/attributes using the column value.

Element/Attribute names (QName) have a set of rules (http://www.w3.org/TR/REC-xml-names/);
if you could freely map column values to name you could end up with invalid XML.

Are you sure you need a so highly unstructured result?

If you are sure that the values do not violate QName restrictions you could post process the query result using XSLT.

Here the steps:

- Create new DB to XML Data Source
- Fill the server info to access the SQLServer sample database "pub"
- Type the following query

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "title_id",t.title_id),
XMLELEMENT(name "title",t.title)
)
FROM pubs.dbo.titles t


- Execute the query; you should see a similar result

<?xml version='1.0' encoding='UTF-8'?>
<root>
<row><title_id>PC1035</title_id><title>But Is It User Friendly?</title></row>
...
</root>


- File -> save as mydb.rdbxml

- File -> New XSLT: Text Editor

- Choose db:///mydb.rdbxml as Source XML

- Type the following XSLT code

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<root>
<xsl:for-each select="root/row">
<xsl:element name="{title_id}">
<xsl:value-of select="title"/>
</xsl:element>
</xsl:for-each>
</root>
</xsl:template>
</xsl:stylesheet>

When executing you should see

<?xml version='1.0' ?>
<root>
<PC1035>But Is It User Friendly?</PC1035>
...
</root>

Hope this helps
Ivan

Postnext
Jim ByrneSubject: Using row data to populate XMLELEMENT name
Author: Jim Byrne
Date: 26 Jan 2005 11:14 AM
Ivan

Unfortunately the highly unstructured result is due to adherence to a
very strict tiered development protocol whereby I have access to data
but not the resulting database structure or application code.

I must adhere to an XML schema that contains approximately 100
"optional" tags of which exactly none or 1 must be present for each
master record. I had hoped to solve this without any post XSLT
processing, mostly due to lack of experience with it. But since we're
into the XSLT code, I think the code you provided will work very nicely
and I'll report back with results.

One alternative, if you don't mind a final follow-up, was conditional
processing within the DB to XML template to only build the tag if a
condition matched the field data value for the row. I don't see any
conditional processing capabilities within DB to XML? Am I missing
something? Is this a possible new feature candidate?

If not, then I was thinking I would just build all 100 tags for each
record and then use "if" or "choose" conditional processing within
XSLT to remove the empty tags? This of course requires considerable work
building numerous tags that will ultimately be dropped. Any thoughts
on making this alternative more efficient?

Thanks!

Postnext
Ivan PedruzziSubject: Using row data to populate XMLELEMENT name
Author: Ivan Pedruzzi
Date: 26 Jan 2005 11:40 AM
>One alternative, if you don't
>mind a final follow-up, was
>conditional
>processing within the DB to
>XML template to only build the
>tag if a
>condition matched the field
>data value for the row. I
>don't see any


You could use WHERE to filter out rows where the field
you are interested is NULL.
The following query removes 2 rows (MC3026, PC9999) because the field royalty is NULL

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "title_id",t.title_id),
XMLELEMENT(name "title",t.title)
)
FROM pubs.dbo.titles t
WHERE NOT T.royalty IS NULL




>

Posttop
Jim ByrneSubject: Using row data to populate XMLELEMENT name
Author: Jim Byrne
Date: 26 Jan 2005 01:53 PM
Ivan,

Thanks for all the assistance. The XSLT solution worked great, I won't
shy away from this powerfull but as yet unexplored territory in the
future. Your most recent comment, however, hinted at a subquery that
worked perfect. When the condition is not met, the entire tag and
underlying subtags are not written to the XML file. I included the
following excerpt in hopes it will provide someone with a similar
problem a bit of assistance.

SELECT
XMLELEMENT(name "Employee",
XMLATTRIBUTES(t.GlobalEmpID As "EmployeeID",
t.ActLevel As "ActivityLevel",
t.RegDate As "RegistrationDate"),
XMLELEMENT(name "Registrations",
XMLELEMENT(name "Registration",
XMLATTRIBUTES(t.ContDate As "ContactDate",
t.RegDate AS "RegistrationDate",
t.GlobalRegID As "RegistrationID"),
XMLELEMENT(name "ResponseElement",
XMLELEMENT(name "LastName",
XMLELEMENT(name "ResponseTxt",t.LastName)
)
),
XMLELEMENT(name "ResponseElement",
XMLELEMENT(name "FirstName",
XMLELEMENT(name "ResponseTxt",t.FirstName)
)
),
(Select
XMLELEMENT(name "Condition1",
XMLELEMENT(name "ConditionComment",t.CondComment),
XMLELEMENT(name "ClassDescription")
)
From CompData.dbo.XML_EmpData c1
Where c1.joinid=t.joinid And t.Cond = 'Condition1'
),
(Select
XMLELEMENT(name "Condition2",
XMLELEMENT(name "ConditionComment",t.CondComment),
XMLELEMENT(name "ClassDescription")
)
From CompData.dbo.XML_ EmpData c2
Where c2.joinid =t.joinid And t. Cond = 'Condition2'
),
XMLELEMENT(name "ResponseElement",
XMLELEMENT(name "EmpID",
XMLELEMENT(name "ResponseTxt",t.EmpID)
)
)
)
)
)
FROM CompData.dbo.XML_ EmpData t

Much appreciated once again...Jim

   
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.