How to create SQL from XSD file

Hello,
I am looking for a way to build an SQL database from an XML schema.1. Try XSLT
2. Read XML using XML Dom, then do a loop -thru|||Also, you may want to look at the SchemaGen option of the SQLXML XML
Bulkload facility. It can give you a first cut at generating the table
definitions.
Best regards
Michael
"Neo" <pravinsable@.gmail.com> wrote in message
news:1144871606.564411.290310@.u72g2000cwu.googlegroups.com...
> Hello,
> I am looking for a way to build an SQL database from an XML schema.
>sql

How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...sql

How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?
Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...

How to Create an XSD/XML Schema file?

I am trying to load the following XML file into a database table. I
receive the file from an external source exactly as follows:
<list type='full' level='state' val='CA'>
<ac val='714'>
<ph val='2011882' />
<ph val='2013327' />
<ph val='2016543' />
<ph val='2017632' />
<ph val='2017656' />
<ph val='2018788' />
<ph val='2018211' />
<ph val='2019345' />
</ac>
</list>
I have a SQL Server 2000 running with a Table named PHONE that has only
one column:
PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
I am new to XML as it works with SQL Server so I have the following
issues:
(1) Because the external file can have millions of PH records, I would
like to load the data into the SQL Server table using OPENXML and the
SQLXMLBulkLoad object (I understand this is a desirable option for
performance).
(2) How should the XSD Mapping Schema file be formatted to
combine/merge the AC and PH values into the PHONE table as one piece of
data?
(3) The bottom line is 'What will the XSD Mapping Schema file look
like'?
Any help would be greatly appreciated.> I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
Since I am a developer, I am kind of weak on these XML-related features of
SQL Server. But since no one else has answered your post, I will tell you
what I would try as a developer.
In any of the .NET programming languages, you have this thing called a
DataSet object, which represents a series of DataTable objects. A DataTable
represents a resultset, like you would get from a SELECT statement. A
DataSet can contain any number of DataTables, and can even enforce
referential integrity between them. A DataSet object has a OpenXML or
ReadXML method (I forget which -- I haven't slept in days) where you can
pass the filename of your XML file, and your DataSet will be populated with
the relational structure and data that's represented inside the XML file.
Then you enumerate all the DataTable objects inside the DataSet.Tables
collection. For each DataTable, you build a InsertCommand or UpdateCommand
for a DataAdapter (the thing that issues commands to the data store)
Then you say DataSet.Update() and presto, all your data is in the database.
In theory.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>
Here's an example of an XSD for the document you posted. Many XML utilities
will generate schemas automatically for you. This one I produced using SQL
Server 2005 Integration Services.
An XSD only validates the format. It doesn't perform any transformations.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="list">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ac">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="ph">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedInt"
use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="val" type="xs:unsignedShort" use="optional"
/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="type" type="xs:string" use="optional" />
<xs:attribute name="level" type="xs:string" use="optional" />
<xs:attribute name="val" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also this free tool (from MS):
http://apps.gotdotnet.com/xmltools/...e/overview.html
http://www.gotdotnet.com/Community/...0c-f24b91e47228
-oj
<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>|||> http://apps.gotdotnet.com/xmltools/...e/overview.html
WOAH! COOL!!
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Hi -
For an example of a XML schema generator utility, see:
http://www.stylusstudio.com/autogen_xsd.html
It shows how to create an XML schema from an XML instance document.
hope that helps.
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.comsql

How to create an xml schema collection from a file

Hello all.
I'm trying to create an xml schema collection from a file, using this code
DROP XML SCHEMA COLLECTION gandy
DECLARE @.xml xml
SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB) as
x
CREATE XML SCHEMA COLLECTION gandy AS @.xml
The problem is that after running this statements SQL Server 2005 gives this
error
Msg 2378, Level 16, State 1, Line 4
Expected XML schema document
What is the problem?
The xslt document is well formed and working (I used it with Altova XML Spy
to apply the transformation).
Thanks
André Giltry something like this
declare @.string varchar(MAX)
declare @.xml varchar(MAX)
select @.string ='Create xml Schema Collection MySchemaCollections as '''
SELECT @.xml = replace(BulkColumn,CHAR(39),CHAR(39)+CHA
R(39)) FROM
openrowset(Bulk ''P04-S01.xsl'', SINGLE_BLOB) as x
select @.string=@.string +@.xml +''''
--print @.string
exec(@.string)
I am not sure about the wisdom of doing something like this though.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andr Gil" <AndrGil@.discussions.microsoft.com> wrote in message
news:8E8AB17D-4D56-442B-A9D1-0459E86A1215@.microsoft.com...
> Hello all.
> I'm trying to create an xml schema collection from a file, using this code
> DROP XML SCHEMA COLLECTION gandy
> DECLARE @.xml xml
> SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB)
> as x
> CREATE XML SCHEMA COLLECTION gandy AS @.xml
> The problem is that after running this statements SQL Server 2005 gives
> this
> error
> Msg 2378, Level 16, State 1, Line 4
> Expected XML schema document
> What is the problem?
> The xslt document is well formed and working (I used it with Altova XML
> Spy
> to apply the transformation).
> Thanks
> Andr Gil|||Interrupting my skiing :-)
Why would you want to load an XSLT transformation as a schema file? They
have nothing in common.
Try loading an actual XML Schema file...
Best regards
Michael
"Andr Gil" <AndrGil@.discussions.microsoft.com> wrote in message
news:8E8AB17D-4D56-442B-A9D1-0459E86A1215@.microsoft.com...
> Hello all.
> I'm trying to create an xml schema collection from a file, using this code
> DROP XML SCHEMA COLLECTION gandy
> DECLARE @.xml xml
> SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB)
> as x
> CREATE XML SCHEMA COLLECTION gandy AS @.xml
> The problem is that after running this statements SQL Server 2005 gives
> this
> error
> Msg 2378, Level 16, State 1, Line 4
> Expected XML schema document
> What is the problem?
> The xslt document is well formed and working (I used it with Altova XML
> Spy
> to apply the transformation).
> Thanks
> Andr Gil

How to create an xml schema collection from a file

Hello all.
I'm trying to create an xml schema collection from a file, using this code
DROP XML SCHEMA COLLECTION gandy
DECLARE @.xml xml
SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB) as x
CREATE XML SCHEMA COLLECTION gandy AS @.xml
The problem is that after running this statements SQL Server 2005 gives this
error
Msg 2378, Level 16, State 1, Line 4
Expected XML schema document
What is the problem?
The xslt document is well formed and working (I used it with Altova XML Spy
to apply the transformation).
Thanks
André Gil
try something like this
declare @.string varchar(MAX)
declare @.xml varchar(MAX)
select @.string ='Create xml Schema Collection MySchemaCollections as '''
SELECT @.xml = replace(BulkColumn,CHAR(39),CHAR(39)+CHAR(39)) FROM
openrowset(Bulk ''P04-S01.xsl'', SINGLE_BLOB) as x
select @.string=@.string +@.xml +''''
--print @.string
exec(@.string)
I am not sure about the wisdom of doing something like this though.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andr Gil" <AndrGil@.discussions.microsoft.com> wrote in message
news:8E8AB17D-4D56-442B-A9D1-0459E86A1215@.microsoft.com...
> Hello all.
> I'm trying to create an xml schema collection from a file, using this code
> DROP XML SCHEMA COLLECTION gandy
> DECLARE @.xml xml
> SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB)
> as x
> CREATE XML SCHEMA COLLECTION gandy AS @.xml
> The problem is that after running this statements SQL Server 2005 gives
> this
> error
> Msg 2378, Level 16, State 1, Line 4
> Expected XML schema document
> What is the problem?
> The xslt document is well formed and working (I used it with Altova XML
> Spy
> to apply the transformation).
> Thanks
> Andr Gil
|||Interrupting my skiing :-)
Why would you want to load an XSLT transformation as a schema file? They
have nothing in common.
Try loading an actual XML Schema file...
Best regards
Michael
"Andr Gil" <AndrGil@.discussions.microsoft.com> wrote in message
news:8E8AB17D-4D56-442B-A9D1-0459E86A1215@.microsoft.com...
> Hello all.
> I'm trying to create an xml schema collection from a file, using this code
> DROP XML SCHEMA COLLECTION gandy
> DECLARE @.xml xml
> SELECT @.xml = BulkColumn FROM openrowset(Bulk 'P04-S01.xsl', SINGLE_BLOB)
> as x
> CREATE XML SCHEMA COLLECTION gandy AS @.xml
> The problem is that after running this statements SQL Server 2005 gives
> this
> error
> Msg 2378, Level 16, State 1, Line 4
> Expected XML schema document
> What is the problem?
> The xslt document is well formed and working (I used it with Altova XML
> Spy
> to apply the transformation).
> Thanks
> Andr Gil

How to Create a Report in SSRS from Local XML File

Hi

i need to create a Report from XML file which available in my current folder.

can anybody suggest How to create a Data source from XML file and How to make a query for that.

Thanks

Mahesh

Are you doing this in an application with ReportViewer in LocalMode? Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

>L<

|||

Hi thanks for ur reply

now we have chenged the way i need to prepare a report

One web service will return a DataSet

based on that Dataset i need prepare a report.

Thanks

Mahesh

|||

hi Mahesh,

That change doesn't really change anything about my first question, although it answers the second one<g>.

The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side. Are you writing a web-application, a win-forms application, or what? Are you using the reportviewer client control?

If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.) If you need to do this, we will discuss further.

If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first. In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

Hope this helps

>L<

|||

Hi Lisa

Thanks for giving more information

I need to prepare a Server side Report .

with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

for this they are given me a webservice link that will return a Data set

based on that i need to prepare a Report.

Previously i know how to prepare a report from Sql server Database.

Thanks.

Mahesh

|||

OK. If the webservice returns a dataset, then it returns it as an XML message. You should be fine with this in a server-side report.

Define the datasource as follows:

The DataSource is of type XML.
The connection string for the web service datasource is in fact the URL for the web service! (http://...) For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style. You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help.|||

Hi

Lisa

i was struck with writing query

i got the webservice responce

<?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://tempuri.org/">
- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="Project">
- <xs:complexType>
- <xsequence>
<xs:element name="ProjectUID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="ProjectName" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
- <diffgriffgram xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlnsiffgr="urnchemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Project diffgr:id="Project1" msdata:rowOrder="0">
<ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
<ProjectName>Reports</ProjectName>
</Project>
</NewDataSet>
</diffgriffgram>
</DataSet>

In DataSet dialog Box i have taken a query

first i tried with "*" only it is showing error

an tried with this

<Query>
<SoapAction>
http://tempuri.org/GetAttributes
</SoapAction>
<Method Namespace="http://tempuri.org/"
Name="GetAttributes">
</Method>
</Query>

It is also not displaying the values of ProjectUid and ProjectName.

can u help me how to write the query for this type of WebService Responce.

Thanks

Mahesh

|||

Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

Either way... To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods. You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way.

It is not the only way. Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

I think they both show you examples of query syntax.

Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

Other references describing other aspects that I have found helpful.
http://technet.microsoft.com/en-us/library/aa964129.aspx
http://channel9.msdn.com/ShowPost.aspx?PostID=137650

>L<

How to Create a Report in SSRS from Local XML File

Hi

i need to create a Report from XML file which available in my current folder.

can anybody suggest How to create a Data source from XML file and How to make a query for that.

Thanks

Mahesh

Are you doing this in an application with ReportViewer in LocalMode? Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

>L<

|||

Hi thanks for ur reply

now we have chenged the way i need to prepare a report

One web service will return a DataSet

based on that Dataset i need prepare a report.

Thanks

Mahesh

|||

hi Mahesh,

That change doesn't really change anything about my first question, although it answers the second one<g>.

The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side. Are you writing a web-application, a win-forms application, or what? Are you using the reportviewer client control?

If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.) If you need to do this, we will discuss further.

If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first. In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

Hope this helps

>L<

|||

Hi Lisa

Thanks for giving more information

I need to prepare a Server side Report .

with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

for this they are given me a webservice link that will return a Data set

based on that i need to prepare a Report.

Previously i know how to prepare a report from Sql server Database.

Thanks.

Mahesh

|||

OK. If the webservice returns a dataset, then it returns it as an XML message. You should be fine with this in a server-side report.

Define the datasource as follows:

The DataSource is of type XML.
The connection string for the web service datasource is in fact the URL for the web service! (/) For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style. You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help.|||

Hi

Lisa

i was struck with writing query

i got the webservice responce

<?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://tempuri.org/">
- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="Project">
- <xs:complexType>
- <xsequence>
<xs:element name="ProjectUID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="ProjectName" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
- <diffgriffgram xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlnsiffgr="urnchemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Project diffgr:id="Project1" msdata:rowOrder="0">
<ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
<ProjectName>Reports</ProjectName>
</Project>
</NewDataSet>
</diffgriffgram>
</DataSet>

In DataSet dialog Box i have taken a query

first i tried with "*" only it is showing error

an tried with this

<Query>
<SoapAction>
http://tempuri.org/GetAttributes
</SoapAction>
<Method Namespace="http://tempuri.org/"
Name="GetAttributes">
</Method>
</Query>

It is also not displaying the values of ProjectUid and ProjectName.

can u help me how to write the query for this type of WebService Responce.

Thanks

Mahesh

|||

Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

Either way... To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods. You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way.

It is not the only way. Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

I think they both show you examples of query syntax.

Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

Other references describing other aspects that I have found helpful.
http://technet.microsoft.com/en-us/library/aa964129.aspx
http://channel9.msdn.com/ShowPost.aspx?PostID=137650

>L<

How to create a Mapping file for this XML file

<Item>
<UPC></UPC>
<ShortSku>400100084564</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>650FORK</ItemID>
<StoreData storenum="1">
<Onhand>1</Onhand>
<AvgCost>85.00</AvgCost>
<CurrRetail>149.95</CurrRetail>
</StoreData>
</Item>
<Item>
<UPC></UPC>
<ShortSku>400100084571</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>TRIDENT</ItemID>
<StoreData storenum="1">
<Onhand>0</Onhand>
<AvgCost>0.00</AvgCost>
<CurrRetail>2000.00</CurrRetail>
</StoreData>
</Item>
Would anyone know what the file would be which coulkd map this to two tables.
Item and Store with filed names identical to the string names in the xml
files.Is there a way to generate the mapping files automatically?
Thanks In anticipation.
Ajay
Hello,
Your xsd schema should look like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Item_Store" parent="Item" parent-key="ItemID"
child-key="ItemID" child="StoreData"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Item" sql:relation="Item">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="UPC" type="xsd:string"/>
<xsd:element name="ShortSKU" type="xsd:int"/>
<xsd:element name="Vendor" type="xsd:string"/>
<xsd:element name="ItemID" type="xsd:string"/>
<xsd:element name="StoreData" sql:relation="StoreData"
sql:relationship="Item_Store">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Onhand" type="xsd:int"/>
<xsd:element name="AvgCost" type="xsd:decimal"/>
<xsd:element name="CurrRetail" type="xsd:decimal"/>
</xsd:sequence>
<xsd:attribute name="storenum" type="xsd:int"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Best regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

How to create a Mapping file for this XML file

<Item>
<UPC></UPC>
<ShortSku>400100084564</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>650FORK</ItemID>
<StoreData storenum="1">
<Onhand>1</Onhand>
<AvgCost>85.00</AvgCost>
<CurrRetail>149.95</CurrRetail>
</StoreData>
</Item>
<Item>
<UPC></UPC>
<ShortSku>400100084571</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>TRIDENT</ItemID>
<StoreData storenum="1">
<Onhand>0</Onhand>
<AvgCost>0.00</AvgCost>
<CurrRetail>2000.00</CurrRetail>
</StoreData>
</Item>
Would anyone know what the file would be which coulkd map this to two tables
.
Item and Store with filed names identical to the string names in the xml
files.Is there a way to generate the mapping files automatically?
Thanks In anticipation.
AjayHello,
Your xsd schema should look like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Item_Store" parent="Item" parent-key="ItemID"
child-key="ItemID" child="StoreData"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Item" sql:relation="Item">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="UPC" type="xsd:string"/>
<xsd:element name="ShortSKU" type="xsd:int"/>
<xsd:element name="Vendor" type="xsd:string"/>
<xsd:element name="ItemID" type="xsd:string"/>
<xsd:element name="StoreData" sql:relation="StoreData"
sql:relationship="Item_Store">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Onhand" type="xsd:int"/>
<xsd:element name="AvgCost" type="xsd:decimal"/>
<xsd:element name="CurrRetail" type="xsd:decimal"/>
</xsd:sequence>
<xsd:attribute name="storenum" type="xsd:int"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Best regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .