Showing posts with label newbie. Show all posts
Showing posts with label newbie. Show all posts

Wednesday, March 7, 2012

Quick FOR XML EXPLICIT question - newbie

Hello.
I need to produce:
<AppForm>
<Forename Box =1>John</Forename>
<Surname Box=2>Smith</Surname>
</AppForm>
<AppForm>
<Forename Box =1>Peter</Forename>
<Surname Box=2>Jones</Surname>
</AppForm>
Where Box is like a box number on an application form and is the same
fixed value for every record.
In my SELECT Tag = 1, Parent = NULL,
I have
NULL AS [AppForm!4!Forename!element],
NULL AS [AppForm!4!Surname!element],
And later in SELECT Tag = 4, Parent = 1,
Forename AS [AppForm!4!Forename!element],
Surname AS [AppForm!4!Surname!element],
>From tbl... etc. etc.
Thank you if you can help me.
Since the name elements have attributes, they need to be treated as
so-called entity elements, meaning that they need their own level in an
explicit mode query. I give you an example below with the EXPLICIT mode and
one with the PATH mode available in SQL Server 2005.
create table customers(custid int, fname nvarchar(100), sname nvarchar(100))
go
insert into customers
select 100, N'John', N'Smith'
union
select 200, N'Peter', N'Jones'
go
select 1 as tag, NULL as parent,
custid as "AppForm!1!!hide",
NULL as "Forename!2!Box",
NULL as "Forename!2!",
NULL as "Surname!3!Box",
NULL as "Surname!3!"
from customers
union all
select 2, 1,
custid,
1, fname,
NULL, NULL
from customers
union all
select 3, 1,
custid,
NULL, NULL,
2, sname
from customers
order by "AppForm!1!!hide", tag
for xml explicit
go
select 1 as "Forename/@.Box", fname as "Forename/text()",
2 as "Surname/@.Box", sname as "Surname/text()"
from customers
for xml path('AppForm')
HTH
Michael
<mrjjohnson@.hotmail.com> wrote in message
news:1135770606.526554.327570@.g49g2000cwa.googlegr oups.com...
> Hello.
> I need to produce:
> <AppForm>
> <Forename Box =1>John</Forename>
> <Surname Box=2>Smith</Surname>
> </AppForm>
> <AppForm>
> <Forename Box =1>Peter</Forename>
> <Surname Box=2>Jones</Surname>
> </AppForm>
> Where Box is like a box number on an application form and is the same
> fixed value for every record.
> In my SELECT Tag = 1, Parent = NULL,
> I have
> NULL AS [AppForm!4!Forename!element],
> NULL AS [AppForm!4!Surname!element],
> And later in SELECT Tag = 4, Parent = 1,
> Forename AS [AppForm!4!Forename!element],
> Surname AS [AppForm!4!Surname!element],
> Thank you if you can help me.
>
|||Michael Rys [MSFT] wrote:
> Since the name elements have attributes, they need to be treated as
> so-called entity elements, meaning that they need their own level in an
> explicit mode query. I give you an example below with the EXPLICIT mode and
> one with the PATH mode available in SQL Server 2005.
>
<snip>
Thanks very much. I'll have a look at that.
Thanks again.

Quick FOR XML EXPLICIT question - newbie

Hello.
I need to produce:
<AppForm>
<Forename Box =1>John</Forename>
<Surname Box=2>Smith</Surname>
</AppForm>
<AppForm>
<Forename Box =1>Peter</Forename>
<Surname Box=2>Jones</Surname>
</AppForm>
Where Box is like a box number on an application form and is the same
fixed value for every record.
In my SELECT Tag = 1, Parent = NULL,
I have
NULL AS [AppForm!4!Forename!element],
NULL AS [AppForm!4!Surname!element],
And later in SELECT Tag = 4, Parent = 1,
Forename AS [AppForm!4!Forename!element],
Surname AS [AppForm!4!Surname!element],ed">
>From tbl... etc. etc.
Thank you if you can help me.Since the name elements have attributes, they need to be treated as
so-called entity elements, meaning that they need their own level in an
explicit mode query. I give you an example below with the EXPLICIT mode and
one with the PATH mode available in SQL Server 2005.
create table customers(custid int, fname nvarchar(100), sname nvarchar(100))
go
insert into customers
select 100, N'John', N'Smith'
union
select 200, N'Peter', N'Jones'
go
select 1 as tag, NULL as parent,
custid as "AppForm!1!!hide",
NULL as "Forename!2!Box",
NULL as "Forename!2!",
NULL as "Surname!3!Box",
NULL as "Surname!3!"
from customers
union all
select 2, 1,
custid,
1, fname,
NULL, NULL
from customers
union all
select 3, 1,
custid,
NULL, NULL,
2, sname
from customers
order by "AppForm!1!!hide", tag
for xml explicit
go
select 1 as "Forename/@.Box", fname as "Forename/text()",
2 as "Surname/@.Box", sname as "Surname/text()"
from customers
for xml path('AppForm')
HTH
Michael
<mrjjohnson@.hotmail.com> wrote in message
news:1135770606.526554.327570@.g49g2000cwa.googlegroups.com...
> Hello.
> I need to produce:
> <AppForm>
> <Forename Box =1>John</Forename>
> <Surname Box=2>Smith</Surname>
> </AppForm>
> <AppForm>
> <Forename Box =1>Peter</Forename>
> <Surname Box=2>Jones</Surname>
> </AppForm>
> Where Box is like a box number on an application form and is the same
> fixed value for every record.
> In my SELECT Tag = 1, Parent = NULL,
> I have
> NULL AS [AppForm!4!Forename!element],
> NULL AS [AppForm!4!Surname!element],
> And later in SELECT Tag = 4, Parent = 1,
> Forename AS [AppForm!4!Forename!element],
> Surname AS [AppForm!4!Surname!element],
> Thank you if you can help me.
>|||Michael Rys [MSFT] wrote:
> Since the name elements have attributes, they need to be treated as
> so-called entity elements, meaning that they need their own level in an
> explicit mode query. I give you an example below with the EXPLICIT mode an
d
> one with the PATH mode available in SQL Server 2005.
>
<snip>
Thanks very much. I'll have a look at that.
Thanks again.

Monday, February 20, 2012

Questions on SQLXML

Hello all,
Newbie here and have a few questions. I wish to use the classes
SqlXmlAdapter,SqlXmlCommand,etc in my ASP.NET project but when I add
'using Microsoft.Data.SqlXml', the IDE doesn't see the namespace. I
tried to add the reference but I dont see it.
1. Why is that? Do I have to download and install SqlXml 3.0 SP2? Does
that add the namespace? I searched for Microsoft.Data.SqlXml.dll on my
hard drive and didn't find it.
2. If I do need to install this patch, why can I do a "Select * from
Product FOR XML AUTO" and have it work already? Does SQL need this
patch or is it a development thing.
3. What does this patch change. I don't mind installing it on my
development PC but kinda worried about my production server. Can I
uninstall this if anything goes wrong?
4. Production server is Windows Server 2003. I am using Windows
Professional. Does WinSvr 2003 need this patch?
5. The patch has XML 3.0 in it. Is this install xml 3.0 or 4.0? I have
already installed MSXML 4.0 on my production server, would installing
this patch downgrade it to xml 3.0 if in fact it is install xml 3.0?
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
Some, but not all, answers below.
Best regards
Michael
"Ralph Krausse" <gordingin@.consiliumsoft.com> wrote in message
news:49eb6317.0408191143.37ca057f@.posting.google.c om...
> Hello all,
> Newbie here and have a few questions. I wish to use the classes
> SqlXmlAdapter,SqlXmlCommand,etc in my ASP.NET project but when I add
> 'using Microsoft.Data.SqlXml', the IDE doesn't see the namespace. I
> tried to add the reference but I dont see it.
> 1. Why is that? Do I have to download and install SqlXml 3.0 SP2? Does
> that add the namespace? I searched for Microsoft.Data.SqlXml.dll on my
> hard drive and didn't find it.
Yes, you need SQLXML 3.0 (SP1 or SP2) downloaded.

> 2. If I do need to install this patch, why can I do a "Select * from
> Product FOR XML AUTO" and have it work already? Does SQL need this
> patch or is it a development thing.
That is because FOR XML is a server side command that is executed on the
database server and not part of ASP.Net or any other mid-tier component.

> 3. What does this patch change. I don't mind installing it on my
> development PC but kinda worried about my production server. Can I
> uninstall this if anything goes wrong?
It fixes some bugs and adds identity propagation for Bulkload.
I don't know about uninstall, but I assume that you can.

> 4. Production server is Windows Server 2003. I am using Windows
> Professional. Does WinSvr 2003 need this patch?
WinSvr 2003 does not ship with SQLXML. So you need the ASP.Net enabled
version downloaded.

> 5. The patch has XML 3.0 in it. Is this install xml 3.0 or 4.0? I have
> already installed MSXML 4.0 on my production server, would installing
> this patch downgrade it to xml 3.0 if in fact it is install xml 3.0?
No. MSXML 4.0 should not be affected by you installing SQLXML 3.0.

>
>
>
> Thanks
> Ralph Krausse
> www.consiliumsoft.com
> Use the START button? Then you need CSFastRunII...
> A new kind of application launcher integrated in the taskbar!
> ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg