MS SQL XML - SQLXML - desperately need help..

Author

SQLXML - desperately need help..

matt@mailinator.com

2005-10-27, 9:25 am

hello,

my organization has been pounding our heads against for a wall for a
week trying to figure out how to write a proc for the below xml
document structure, using SQL Secure 2000:

< ArrayOfCustomerCentr
alPartners>
< CustomerCentralPartn
er>
<PartnerCode>AA</PartnerCode>
<ID>6</ID>
<Name>WAMU</Name>
<Logo>logo_wamu.gif</Logo>
<ApplicationStyle>WamuPhoneStyles.css</ApplicationStyle>
<ControlStyle> WAMUPIPControlStyles
.css</ControlStyle>
<PageStyle>WAMUPagesStyles.css</PageStyle>
<IsHeaderDisplayed>true</IsHeaderDisplayed>
<IsCOAFLogoDisplayed>false</IsCOAFLogoDisplayed>
<TopNavItems>
<NavItem AltText="link1" ImageName="1.gif" Height="22" Width="166"
ClickUrl="http://www.google.com"/>
<NavItem AltText="link2" ImageName="2.gif" Height="22" Width="166"
ClickUrl="http://www.google.com"/>
<NavItem AltText="link3" ImageName="3.gif" Height="22" Width="166"
ClickUrl="http://www.google.com"/>
<NavItem AltText="link4" ImageName="4.gif" Height="22" Width="166"
ClickUrl="http://www.google.com"/>
<NavItem AltText="link5" ImageName="5.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
</TopNavItems>
<LeftNavItems>
<NavItem AltText="linkA" ImageName="a.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
<NavItem AltText="linkB" ImageName="b.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
<NavItem AltText="linkC" ImageName="c.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
<NavItem AltText="linkD" ImageName="d.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
<NavItem AltText="linkE" ImageName="e.gif" Height="22" Width="166"
ClickUrl="http://www.yahoo.com"/>
</LeftNavItems>
<FooterText>xxx</FooterText>
</ CustomerCentralPartn
er>
</ ArrayOfCustomerCentr
alPartners>

....can any kind soul take a look at this, and reply w/ how the stored
procedure should look (w/ dummy data/tables, of course)? if you could,
i would offcially be Your Best Friend(tm) for an indiscriminate period
of time.

hunbly, and with thanks...

matt

Kent Tegels

2005-10-27, 9:25 am

Hello matt@mailinator.com,

Consider using FOR XML...EXPLICIT, but as you've probably discovered, that's
painful. XDRs might be acceptable if you live with the IIS requirement.

Or wait for FOR XML... PATH in SQL 2005.

Thanks!

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Michael Rys [MSFT]

2005-10-27, 9:25 am

Do you want to load the data into relational tables? Then look at the SQLXML
XML Bulkload object (can propose even a relational table layout), or at
OpenXML in T-SQL. If you need to come up with your own table structure,
think about how you want to use your data, and whether XML recompositon is
important.

Or do you have existing relational tables that you want to publish as XML?
Then see Kent's answer.

Michael

<matt@mailinator.com> wrote in message
news:1128538631.923274.87390@f14g2000cwb.googlegroups.com...
> hello,
>
> my organization has been pounding our heads against for a wall for a
> week trying to figure out how to write a proc for the below xml
> document structure, using SQL Secure 2000:
>
>
> < ArrayOfCustomerCentr
alPartners>
> < CustomerCentralPartn
er>
> <PartnerCode>AA</PartnerCode>
> <ID>6</ID>
> <Name>WAMU</Name>
> <Logo>logo_wamu.gif</Logo>
> <ApplicationStyle>WamuPhoneStyles.css</ApplicationStyle>
> <ControlStyle> WAMUPIPControlStyles
.css</ControlStyle>
> <PageStyle>WAMUPagesStyles.css</PageStyle>
> <IsHeaderDisplayed>true</IsHeaderDisplayed>
> <IsCOAFLogoDisplayed>false</IsCOAFLogoDisplayed>
> <TopNavItems>
> <NavItem AltText="link1" ImageName="1.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link2" ImageName="2.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link3" ImageName="3.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link4" ImageName="4.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link5" ImageName="5.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> </TopNavItems>
> <LeftNavItems>
> <NavItem AltText="linkA" ImageName="a.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkB" ImageName="b.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkC" ImageName="c.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkD" ImageName="d.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkE" ImageName="e.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> </LeftNavItems>
> <FooterText>xxx</FooterText>
> </ CustomerCentralPartn
er>
> </ ArrayOfCustomerCentr
alPartners>
>
>
> ...can any kind soul take a look at this, and reply w/ how the stored
> procedure should look (w/ dummy data/tables, of course)? if you could,
> i would offcially be Your Best Friend(tm) for an indiscriminate period
> of time.
>
>
> hunbly, and with thanks...
>
> matt
>

matt@mailinator.com

2005-10-27, 9:25 am

sorry, maybe i didnt clarify enough.

the data is already in SQL Server, as relational tables. we are trying
to put it *into* xml format, matching the above structure. we are using
SQLXML's FOR XML EXPLICIT technique -- creating a bunch of joins, of
the tag structure.

but we cant seem to figure out the syntax for all the tag levels, etc.

matt

Kent Tegels

2005-10-27, 9:25 am

It is a mess, I know, especially with the schema you're shooting for. Getting
the tags straight is just part of the problem... if you're coping with any
significant number of rows, the processing time wont be easy either. I suspect
that, and I hate to say this, that you might be better off generating the
XML higher up in the application stack (think getting the data from a proc
and munging it into the XML form you want with .NET instead of FOR XML query).

That's why I asked about using SQL 2005. FOR XML PATH would make this a lot
easier.

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Michael Rys [MSFT]

2005-10-27, 9:25 am

Thanks. Can you please send me the some sample tables and some sample data,
I will take a look.

While Kent is correct, that this will be a query from hell and the SQL
Server 2005 PATH mode would help in maintainability and authoring of the
query, I am doubtful that a mid-tier generation of the XML would necessarily
be faster, given the need to often transport more data than needed between
the tiers in that case.

Best regards
Michael

<matt@mailinator.com> wrote in message
news:1128623050.595234.257980@z14g2000cwz.googlegroups.com...
> sorry, maybe i didnt clarify enough.
>
> the data is already in SQL Server, as relational tables. we are trying
> to put it *into* xml format, matching the above structure. we are using
> SQLXML's FOR XML EXPLICIT technique -- creating a bunch of joins, of
> the tag structure.
>
> but we cant seem to figure out the syntax for all the tag levels, etc.
>
>
> matt
>

Alwik

2005-10-27, 9:25 am

> hello,
>
> my organization has been pounding our heads against for a wall for a
> week trying to figure out how to write a proc for the below xml
> document structure, using SQL Secure 2000:
>
>
> < ArrayOfCustomerCentr
alPartners>
> < CustomerCentralPartn
er>
> <PartnerCode>AA</PartnerCode>
> <ID>6</ID>
> <Name>WAMU</Name>
> <Logo>logo_wamu.gif</Logo>
> <ApplicationStyle>WamuPhoneStyles.css</ApplicationStyle>
> <ControlStyle> WAMUPIPControlStyles
.css</ControlStyle>
> <PageStyle>WAMUPagesStyles.css</PageStyle>
> <IsHeaderDisplayed>true</IsHeaderDisplayed>
> <IsCOAFLogoDisplayed>false</IsCOAFLogoDisplayed>
> <TopNavItems>
> <NavItem AltText="link1" ImageName="1.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link2" ImageName="2.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link3" ImageName="3.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link4" ImageName="4.gif" Height="22" Width="166"
> ClickUrl="http://www.google.com"/>
> <NavItem AltText="link5" ImageName="5.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> </TopNavItems>
> <LeftNavItems>
> <NavItem AltText="linkA" ImageName="a.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkB" ImageName="b.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkC" ImageName="c.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkD" ImageName="d.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> <NavItem AltText="linkE" ImageName="e.gif" Height="22" Width="166"
> ClickUrl="http://www.yahoo.com"/>
> </LeftNavItems>
> <FooterText>xxx</FooterText>
> </ CustomerCentralPartn
er>
> </ ArrayOfCustomerCentr
alPartners>
>
>
> ...can any kind soul take a look at this, and reply w/ how the stored
> procedure should look (w/ dummy data/tables, of course)? if you could,
> i would offcially be Your Best Friend(tm) for an indiscriminate period
> of time.
>
>
I think it is a difficult task to do it in a single explicit for xml qury.
However you can built it from xml fragments and add root
< ArrayOfCustomerCentr
alPartners> during execution of such a query. Here is a
simple sample (sorry for mixture of small and capital letters I am to tired
now...)

-- sample customer table
create table customer (PartnerCode varchar(50), Id int , Name varchar(50),
Logo varchar(50),
ApplicationStyle varchar(50), ControlStyle varchar(50),pagestyl
e
varchar(50),
IsHeaderDisplayed bit, IsCOSFLogoDisplayed bit)

-- sample navigation table id is a fk fro customer
create table navitem (key int, id int, type Varchar(50), alttext
varchar(50),
height int, width int, clickurl varchar(50))*/

-- add some records to customers and navigation (set id properly), set type
to 'top' or 'left'

CREATE PROCEDURE spGenerateOneCustome
r
@id INT
as
select '< CustomerCentralPartn
er>'
SELECT PartnerCode, Id, Name, Logo, ApplicationStyle,
ControlStyle,pagesty
le,
IsHeaderDisplayed, IsCOSFLogoDisplayed FROM Customer where Id = @id
for xml auto, ELEMENTS
select '<TopNavItem>'
SELECT * FROM Navitem WHERE Type = 'Top'AND ID = @Id FOR XML AUTO
select '</TopNavItem>'
select '<LeftNavItem>'
SELECT * FROM Navitem WHERE Type = 'Left'AND ID = @Id FOR XML AUTO
select '</LeftNavItem>'
select '</ CustomerCentralPartn
er>'
go*/

-- execute
exec spGenerateOneCustome
r 1
exec spGenerateOneCustome
r 2

You can execute several sp in a xml query tamplate (but I didnot tried it
myself, if it does not work create another sp with parameter containing list
of customers id and execute spGenerateOneCustome
r inside it in a loop

I hope it helps

Alwik

Alwik

2005-10-27, 9:25 am

Sorry I did not removed '*/' which I used during testing it :)
Alwik

Sponsored Links