connecting to SSAS: xml

Showing posts with label xml. Show all posts

Wednesday, March 21, 2012

Problem getting select data set for XML Auto, Elements into a table variable -

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like

select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.

I can't do a select into a temp table from my select statement for XML Auto.

I can't use select into as well.

I can't enclose my entire select statment in paranthesis to put it in a table variable.

How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:

declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)

declare @.l_XML_Table table (XML_String varchar (8000))

select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'

select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM

where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id

and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID

for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?

problem getting result set through a stored procedure call using VB.

Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql

Friday, March 9, 2012

Problem creating XML with FOR XML PATH -Resolved

Using Northwind as an example this is the XML I would like to create:

However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?

use northwind

select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
orderID 'Customer/Orders/Order/@.orderID',
convert(nvarchar(10),OrderDate,101) 'Customer/Orders/Order/@.orderdate'
from Customers
join Orders on Customers.CustomerID =Orders.CustomerID
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')

Partial Result of query:

<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
</Orders>
</Customer>
</CustomerOrders>
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10692" orderdate="10/03/1997" />
</Orders>
</Customer>
</CustomerOrders>...

Mickey:

My first pass at the problem looks like this:

Code Snippet

declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'

declare @.orders table
( customerId varchar(12),
orderId integer,
orderDate datetime
)
insert into @.orders
select 'ALFKI', 10643, '8/25/1997' union all
select 'ALFKI', 10692, '10/3/1997' union all
select 'ALFKI', 10702, '10/13/1997' union all
select 'ALFKI', 10835, '1/15/98' union all
select 'ALFKI', 10952, '3/17/98' union all
select 'ALFKI', 11011, '4/9/98'
--select * from @.orders

select replace(replace(
(
select customerId 'Customer/@.CustomerID',
companyName 'Customer/@.CompanyName',
( select orderId 'Order/@.OrderID',
convert(varchar, orderDate, 101) 'Order/@.orderdate'
from @.orders b
for xml path('')
) 'Customer/Orders'
from @.customers
for xml path(''), root('CustomerOrders')
), '<', '<'), '>', '>')
as theXml

/*
theXml

<CustomerOrders><Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"><Orders><Order OrderID="10643" orderdate="08/25/1997"/><Order OrderID="10692" orderdate="10/03/1997"/><Order OrderID="10702" orderdate="10/13/1997"/><Order OrderID="10835" orderdate="01/15/1998"/><Order OrderID="10952" orderdate="03/17/1998"/><Order OrderID="11011" orderdate="04/09/1998"/></Orders></Customer></CustomerOrders>
*/

/* Manually Reformatted:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order OrderID="10643" orderdate="08/25/1997"/>
<Order OrderID="10692" orderdate="10/03/1997"/>
<Order OrderID="10702" orderdate="10/13/1997"/>
<Order OrderID="10835" orderdate="01/15/1998"/>
<Order OrderID="10952" orderdate="03/17/1998"/>
<Order OrderID="11011" orderdate="04/09/1998"/>
</Orders>
</Customer>
</CustomerOrders>
*/

|||

After some research I was able to find a solution using a subselect.

Here is the code. Thanks to anyone who spent any time on this.

use northwind

select

Customers.CustomerID 'Customer/@.CustomerID',

CompanyName 'Customer/@.CompanyName',

(select

orderID '@.orderID',

convert(nvarchar(10),OrderDate,101) '@.orderdate'

from Orders

where Customers.CustomerID =Orders.CustomerID

FOR XML PATH ('Order'), Type ) 'Customer/Orders'

from Customers

where Customers.CustomerID='ALFKI'

FOR XML PATH ('CustomerOrders')

Problem creating XML with FOR XML PATH

Using Northwind as an example this is the XML I would like to create:

However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?

use northwind

Partial Result of query:

Mickey:

My first pass at the problem looks like this:

Code Snippet

declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'

/*
theXml

|||

After some research I was able to find a solution using a subselect.

Here is the code. Thanks to anyone who spent any time on this.

use northwind

select

Customers.CustomerID 'Customer/@.CustomerID',

CompanyName 'Customer/@.CompanyName',

(select

orderID '@.orderID',

convert(nvarchar(10),OrderDate,101) '@.orderdate'

from Orders

where Customers.CustomerID =Orders.CustomerID

FOR XML PATH ('Order'), Type ) 'Customer/Orders'

from Customers

where Customers.CustomerID='ALFKI'

FOR XML PATH ('CustomerOrders')

Saturday, February 25, 2012

Problem Converting XML to table in SQL server

Hi There,
I'm new to the .Net environment, and I am currently trying to convert an xml document and its appropriate schema, to a dataset then with this Dataset I would like to load it into the database. This XML file is generated from the Access 2000 format. Then sent over the internet where it is received by an HttpFileCollection object, then the schema and xml file are loaded into a dataset.
I have created a new SQL database in which I imported the table required from Access, so in theory the table that the xml is generated from should be the same as the table that the xml is destined for.
I am getting the data loaded into the dataset however, when I try to execute the data adapter update command

oda.Update(myDS, "MSGLOBAL") it is throwing an Exception, in this exception the message is telling me that there is a problem near the key word 'on'.
Now I have added a watch window to monitor the sql statements, now the oda has an insert statement generated from a query builder, however this same query builder will not create either an update or delete command.
I hope this is a good enough explanation to get a few answers, or a possible example, it seems to be a topic that is quite difficult to research.
Thank you, and if you require more info please indicate this.
Damon

Http://www.ezywiz.biz

Access 2000 is barely relational and XML that old is not relational either so use DTS to move the data to SQL Server. Try the link below for DTS samples. Hope this helps.
http://www.sqldts.com|||Thanks Gary for your quick response.
I'll have a look at the link with the SQL examples.
The dataset isn't coming directly from Access 2000 though, it is coming from the XML file extracted from the Access database on the client side and received at the server. Is DTS still an appropriate option.
Thanks...Damon|||You can move the data to SQL Server with DTS and get the dataset from SQL Server. Hope this helps.|||

Choices aplenty.

1. You can code your own inserts statement rather than using the command builder

2. Use SQL/XML updategrams

3. Use SQL OpenXML and send the XML in

4. Process the XML and send in straight forward insert commands

5. Process the dataset and send in straight forward insert command

Do you really need to use a dataset?

Wednesday, March 21, 2012

Problem getting select data set for XML Auto, Elements into a table variable -

problem getting result set through a stored procedure call using VB.

Friday, March 9, 2012

Problem creating XML with FOR XML PATH -Resolved

Problem creating XML with FOR XML PATH

Saturday, February 25, 2012

Problem Converting XML to table in SQL server

connecting to SSAS

Blog Archive

About Me