|
|
 next
|
  Subject: 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
|
 top
|
  Subject: 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
|
|
|
|
|
