Create tables in SQL2K from XSD schema?

I am in need of some help!
Relatively new to the whole XML thing as far as SQL server goes...
I have an XSD schema that describes a bunch of tables with various
relationships. Is there a way for me to create tables and relationships based
on the XSD file.
I have tried the net but seem to be unable to find anything. Any help would
be greatly appreciated.
Matt
If at first you don't succeed... Hide the evidence that you tried!
In theory yes. Please see the link
http://support.microsoft.com/default...;en-us;316005. You have to
set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
hand is a very daunting task and I never able to make it work for complex
shcemas. Are you using some tool to generate the XSD shcema annotaion?
"Director - Minvent" wrote:

> I am in need of some help!
> Relatively new to the whole XML thing as far as SQL server goes...
> I have an XSD schema that describes a bunch of tables with various
> relationships. Is there a way for me to create tables and relationships based
> on the XSD file.
> I have tried the net but seem to be unable to find anything. Any help would
> be greatly appreciated.
> Matt
> --
> If at first you don't succeed... Hide the evidence that you tried!
|||The schema was written using XMLspy by one of the guys at Agilent
technologies (HP) as part of one of their products... Unfortunately it is not
intended for use and therefore is not supported, but hopefully tested prior
to release!
Basically it is the definition of an xml output file from an analytical
instrument which I want to capture data from. Hence trying to create a
database based on its definition.
I'm using VB.net and desperately trying to find a namespace containing
sqlxml?!? I am probably being really stupid... which wouldn't suprise me!.
Cheers
"Rashid" wrote:
[vbcol=seagreen]
> In theory yes. Please see the link
> http://support.microsoft.com/default...;en-us;316005. You have to
> set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
> hand is a very daunting task and I never able to make it work for complex
> shcemas. Are you using some tool to generate the XSD shcema annotaion?
> "Director - Minvent" wrote:

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

sql

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

create table from xml schema

Hi everybody!
I need to create a table in SQL server from the xml schema of a file. Is
it possible?
Thanks a lot.
Anahi
Download SQLXML 3.0 from the MS SQL Server Website.
Check out the SQLXML Bulk Load component.
I think you can write a script in a DTS package and load data.
check out this article:
http://support.microsoft.com/default...b;en-us;316005
until MS Support replies.
"Anahi Luduea" <anahi@.optience.com> wrote in message
news:%23ANr9j0cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi everybody!
> I need to create a table in SQL server from the xml schema of a file. Is
> it possible?
> Thanks a lot.
> Anahi
>

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
Ganesh
Hi
If I understand
http://msdn.microsoft.com/library/de.../sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:
> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/d...r />
k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefiniti
on. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements o
f
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

Create related tables from XML schema

I'm new to XML.
I received a schema from one of our vendors (please see below)
I would like to create SQLserver tables required for exporting XML file
later using this schema.
Is there a way to do this in SQLserver?
Thanks
Bill
<CDData> XML Schema
<?xml version="1.0"?>
<!-- CD Schema Basic version 3.2.1 3/2/2004 -->
<!-- SubElements and Attributes for each Element must appear in order
shown -->
<Schema name="CD_Routedata_XML_Schema"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name='PRODUCT' content='eltOnly' model='closed'>
<AttributeType name='PRODNAME' dt:type='string' required='no'/>
<AttributeType name='PRODID' dt:type='string' required='no'/>
<AttributeType name='INVOICE' dt:type='string' required='no'/>
<AttributeType name='QTY' dt:type='number' required='no'/>
<Attribute type='PRODNAME'/>
<Attribute type='PRODID'/>
<Attribute type='INVOICE'/>
<Attribute type='QTY'/>
</ElementType>
<ElementType name='PRODUCTS' content='eltOnly' model='closed'>
<Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
</ElementType>
<ElementType name='ACCTNAME' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ACCTID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
model='closed'/>
<ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='DURATION' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='DEPART' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='CODE' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='STOP' content='eltOnly' model='closed'>
<AttributeType name='NUM' dt:type='ui2' required='yes'/>
<Attribute type='NUM'/>
<Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
<Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
<Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
<Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
<Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
<Element type='DURATION' minOccurs='0' maxOccurs='1'/>
<Element type='DEPART' minOccurs='0' maxOccurs='1'/>
<Element type='CODE' minOccurs='0' maxOccurs='1'/>
<Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='DRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='CODRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ROUTE' content='eltOnly' model='closed'>
<AttributeType name='DATE' dt:type='datetime' required='yes'/>
<AttributeType name='SITEID' dt:type='string' required='yes'/>
<AttributeType name='ROUTEID' dt:type='string' required='yes'/>
<Attribute type='DATE'/>
<Attribute type='ROUTEID'/>
<Attribute type='SITEID'/>
<Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
<Element type='STOP' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
<AttributeType name='ADD' dt:type='string' required='no'/>
<Attribute type='ADD'/>
<Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='CADECDATA' content='eltOnly' model='closed'>
<Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='BASE' content='eltOnly' model='open'>
<Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
</ElementType>
</Schema>
Look at the SchemaGen option of the SQLXML XML Bulkload object.
Best regards
Michael
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:eCA47z88GHA.3280@.TK2MSFTNGP02.phx.gbl...
> I'm new to XML.
> I received a schema from one of our vendors (please see below)
> I would like to create SQLserver tables required for exporting XML file
> later using this schema.
> Is there a way to do this in SQLserver?
> Thanks
> Bill
> --
> <CDData> XML Schema
> <?xml version="1.0"?>
> <!-- CD Schema Basic version 3.2.1 3/2/2004 -->
> <!-- SubElements and Attributes for each Element must appear in order
> shown -->
> <Schema name="CD_Routedata_XML_Schema"
> xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:datatypes">
> <ElementType name='PRODUCT' content='eltOnly' model='closed'>
> <AttributeType name='PRODNAME' dt:type='string' required='no'/>
> <AttributeType name='PRODID' dt:type='string' required='no'/>
> <AttributeType name='INVOICE' dt:type='string' required='no'/>
> <AttributeType name='QTY' dt:type='number' required='no'/>
> <Attribute type='PRODNAME'/>
> <Attribute type='PRODID'/>
> <Attribute type='INVOICE'/>
> <Attribute type='QTY'/>
> </ElementType>
> <ElementType name='PRODUCTS' content='eltOnly' model='closed'>
> <Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
> </ElementType>
> <ElementType name='ACCTNAME' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ACCTID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
> model='closed'/>
> <ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='DURATION' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='DEPART' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='CODE' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='STOP' content='eltOnly' model='closed'>
> <AttributeType name='NUM' dt:type='ui2' required='yes'/>
> <Attribute type='NUM'/>
> <Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
> <Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
> <Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
> <Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
> <Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
> <Element type='DURATION' minOccurs='0' maxOccurs='1'/>
> <Element type='DEPART' minOccurs='0' maxOccurs='1'/>
> <Element type='CODE' minOccurs='0' maxOccurs='1'/>
> <Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='DRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='CODRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ROUTE' content='eltOnly' model='closed'>
> <AttributeType name='DATE' dt:type='datetime' required='yes'/>
> <AttributeType name='SITEID' dt:type='string' required='yes'/>
> <AttributeType name='ROUTEID' dt:type='string' required='yes'/>
> <Attribute type='DATE'/>
> <Attribute type='ROUTEID'/>
> <Attribute type='SITEID'/>
> <Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
> <Element type='STOP' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
> <AttributeType name='ADD' dt:type='string' required='no'/>
> <Attribute type='ADD'/>
> <Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='CADECDATA' content='eltOnly' model='closed'>
> <Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='BASE' content='eltOnly' model='open'>
> <Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> </Schema>
>

Create Mapping Schema file

What are the tools which are avilable to create the
mapping schema file to load data from XML file to SQL
Server?
Thanks
Kokoy
"Mkokoy" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5a701c44e57$872114f0$a501280a@.phx.gbl...
> What are the tools which are avilable to create the
> mapping schema file to load data from XML file to SQL
> Server?
There aren't a lot of them. I think xmlspy has one http://www.xmlspy.com
Bryant

Create element names from data in "FOR XML PATH" query?

Hi, all. I am writing a stored procedure to create an XML-formatted export from a relational database. I am succeeding for the most part with "FOR XML PATH" queries, thanks to help from these forums, but I've hit a new issue.

I have a table we'll call "facet", and here is a subset of the table's columns:

- facet_id (nvarchar(10))

- facet_type (nvarchar(3))

- facet_value (nvarchar(255))

Part of the XML schema requires that I list these facets, and the element ID is the facet ID. I need to create this:

<facet_id>facet_value</facet_id>

<facet_id>facet_value</facet_id>

...with, of course, both "facet_id" and "facet_value" populated from the database columns. This part of the extract creates subelements to the facet owners, and there is a lower level that contains subelements to some of the facets.

Is there any way to do this? The only alternative I can see is to create a table function to pivot the "facet" table into a horizontal version of itself, but this is ugly for two reasons: performance and the complications it will create when I have to create the subelements to the facets themselves.

Thanks!

I have a similar need. I have a table-valued function that I want to return XML in which the field name itself is defined by data. In my case, these are phone numbers and I want to query a table and return a list

<PrimaryPhone>444-444-4444</PrimaryPhone>
<HomePhone>555-555-5555</HomePhone>

etc., where the node name is defined in a link table.

Best I can come up with so far is something like:

SELECT
'<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

But the special symbols (<, >, etc) are automatically converted to their escaped equivalents so that will not work.

Any advice?|||

cast the string expression to xml should work:

SELECT cast
( '<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' as xml) AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

Create dynamic reports

Hi,

I receive an xml object that represent just about any kind of a table containing unknown amount of text fields, and need to create a Table report out of it, the header fields are not pre-determined.

Is this possible with Crystal reports and in particular in the .Net free addition of Crystal reports ?

if so - HOW ? please explain as articulately as possible

Thanks
. .
|
~

NimCo.You can try using CDO (Crystal Data Objects) to pass info to Crystal Reports. Basically, you populate an array and pass the array to the report.

Look here for an example of CDO in VB 6, maybe you can adapt it to your needs...

http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo

Create Database models from XML Schema (.xsd)

Hi .Net Guru's,

I have an urgent requirement for my project; the issue is mentioned below;
Using .Net(C#/VB.Net) I need to generate/created Database objects from XML schemas.

I don't have any sample xml schema file to give you.
You just imagine you have a sample .xsd file and this .xsd file will be used to create database tables.

Please let me know if you have any queries.

Thanks,
nick

Hi

You could create a DateSet from visual studio and add some TableAdapters to it VS will generate XSD for you.

And there is a free tool XSD2DB Utilitylets you manage database schemas using XSD files.

Hope this helps.

create data source, data source view from XML?

hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?

thanks for you helps

Basically, you need to double check that you can the following: 1) You can connect to the server successfully; 2) The database for containing the target data source and data source view has been created, and you can access it; 3) You specify the connection appropriately.

Please double check your script against the following sample XMLA script for creating Data Source:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>Your Database ID</DatabaseID>

</ParentObject>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xsi:type="RelationalDataSource">

<ID>Your Data source ID</ID>

<Name>Your Data source name</Name>

<ConnectionString>Provider=SQLOLEDB.1;Data Source=Server Name;Integrated Security=SSPI;Initial Catalog= Your Database ID </ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>Default</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Create>

I have marked out the xml elements that you need to pay extra attention. As to the script for data source view, you also need to make sure the related data source exist on the server.

Good luck,