Extracting Sql: Extracting data as xml from 4 tables

Hi,
I am having some difficulty extracting the xml that is required using FOR
XML EXPLICIT and I am wondering if you can help me please?
The target xml format is this;
<?xml version="1.0"?>


<c i="1238" t="1">
<n>Test Phone</n>
<a>1-544-552-8900</a>
<f>0x0022</f>
</c>
<c i="1233" t="1">
<n>Phone Tester</n>
<a>1-788-111-42322</a>
<f>0x0011</f>
</c>
<c i="1234" t="1">
<n>Cell Tester</n>
<a>1-445-255-3339</a>
<f>0x0111</f>
</c>


With any number of P's
each with any number of c's
each c may or may not have 1 each of n, a & f
or another way of looking at it- apart from PK's and FK's
All NOT NULL's will always be present & all NULL values will not be
represented in the resulting file
The data is contained in 3 tables;
TABLE [U]
[UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
[sid] [varchar] (20) NOT NULL
TABLE [P]
[PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
[UID] [int] NOT NULL, (FK)
[num] [varchar] (20) NOT NULL,
[f] [int] NULL,
[x] [int] NULL,
[d] [int] NULL,
[t] [int] NOT NULL
TABLE [c]
[CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
[PID] [int] NOT NULL , (FK)
[i] [varchar] (20) NOT NULL,
[n] [varchar] (20) NULL,
[a] [varchar] (20) NULL,
[f] [varchar] (8) NULL
I have a fourth table which contains pointers to the data that needs
including for a given U.
TABLE [Todo]
[id] [int] IDENTITY (1, 1) NOT NULL, (PK)
[DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
[UID] [int] NULL ,
[PID] [int] NULL ,
[CID] [int] NULL ,
[dir] [Int] NOT NULL
So for the given xml file above the contents of Todo is;
row1=n,[thedate],1,NULL,NULL,"0"
row2=n+1,[thedate],1,1,NULL,"0"
row3=n+1,[thedate],1,1,1,"0"
row4=n+1,[thedate],1,1,2,"0"
row5=n+1,[thedate],1,1,3,"0"
I hope that is clear and that you can help me.
Many thanks
Andy LThis is one of the more complicated dialects; however, check out the
following link--it has quite a bit of detailed syntax information.
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
Sincerely,
Anthony Thomas
"Ultradiv" wrote:
> Hi,
> I am having some difficulty extracting the xml that is required using FOR
> XML EXPLICIT and I am wondering if you can help me please?
> The target xml format is this;
> <?xml version="1.0"?>
> 
> 
> <c i="1238" t="1">
> <n>Test Phone</n>
> <a>1-544-552-8900</a>
> <f>0x0022</f>
> </c>
> <c i="1233" t="1">
> <n>Phone Tester</n>
> <a>1-788-111-42322</a>
> <f>0x0011</f>
> </c>
> <c i="1234" t="1">
> <n>Cell Tester</n>
> <a>1-445-255-3339</a>
> <f>0x0111</f>
> </c>
> 
> 
> With any number of P's
> each with any number of c's
> each c may or may not have 1 each of n, a & f
> or another way of looking at it- apart from PK's and FK's
> All NOT NULL's will always be present & all NULL values will not be
> represented in the resulting file
> The data is contained in 3 tables;
> TABLE [U]
> [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [sid] [varchar] (20) NOT NULL
> TABLE [P]
> [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> [UID] [int] NOT NULL, (FK)
> [num] [varchar] (20) NOT NULL,
> [f] [int] NULL,
> [x] [int] NULL,
> [d] [int] NULL,
> [t] [int] NOT NULL
>
> TABLE [c]
> [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [PID] [int] NOT NULL , (FK)
> [i] [varchar] (20) NOT NULL,
> [n] [varchar] (20) NULL,
> [a] [varchar] (20) NULL,
> [f] [varchar] (8) NULL
>
> I have a fourth table which contains pointers to the data that needs
> including for a given U.
> TABLE [Todo]
> [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> [UID] [int] NULL ,
> [PID] [int] NULL ,
> [CID] [int] NULL ,
> [dir] [Int] NOT NULL
> So for the given xml file above the contents of Todo is;
> row1=n,[thedate],1,NULL,NULL,"0"
> row2=n+1,[thedate],1,1,NULL,"0"
> row3=n+1,[thedate],1,1,1,"0"
> row4=n+1,[thedate],1,1,2,"0"
> row5=n+1,[thedate],1,1,3,"0"
> I hope that is clear and that you can help me.
> Many thanks
> Andy L
>
>
>|||Yeah it is complicated!
Its fine with two queries and UNION ALL
but adding the third part has proved very difficult
Thanks for the reference Anthony, I'll read through to see if there is
anything more than my MS book 'Programming Microsoft SQL Server 2000 with
XML Second edition' and let you know
Cheers,
Andy
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:F890E101-9DE3-4F60-8714-71174E65A39E@.microsoft.com...
> This is one of the more complicated dialects; however, check out the
> following link--it has quite a bit of detailed syntax information.
>
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
> Sincerely,
>
> Anthony Thomas
>
> "Ultradiv" wrote:
> > Hi,
> > I am having some difficulty extracting the xml that is required using
FOR
> > XML EXPLICIT and I am wondering if you can help me please?
> >
> > The target xml format is this;
> >
> > <?xml version="1.0"?>
> > 
> > 
> > <c i="1238" t="1">
> > <n>Test Phone</n>
> > <a>1-544-552-8900</a>
> > <f>0x0022</f>
> > </c>
> > <c i="1233" t="1">
> > <n>Phone Tester</n>
> > <a>1-788-111-42322</a>
> > <f>0x0011</f>
> > </c>
> > <c i="1234" t="1">
> > <n>Cell Tester</n>
> > <a>1-445-255-3339</a>
> > <f>0x0111</f>
> > </c>
> > 
> > 
> >
> > With any number of P's
> > each with any number of c's
> > each c may or may not have 1 each of n, a & f
> > or another way of looking at it- apart from PK's and FK's
> > All NOT NULL's will always be present & all NULL values will not be
> > represented in the resulting file
> >
> > The data is contained in 3 tables;
> >
> > TABLE [U]
> > [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [sid] [varchar] (20) NOT NULL
> >
> > TABLE [P]
> > [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> > [UID] [int] NOT NULL, (FK)
> > [num] [varchar] (20) NOT NULL,
> > [f] [int] NULL,
> > [x] [int] NULL,
> > [d] [int] NULL,
> > [t] [int] NOT NULL
> >
> >
> > TABLE [c]
> > [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [PID] [int] NOT NULL , (FK)
> > [i] [varchar] (20) NOT NULL,
> > [n] [varchar] (20) NULL,
> > [a] [varchar] (20) NULL,
> > [f] [varchar] (8) NULL
> >
> >
> > I have a fourth table which contains pointers to the data that needs
> > including for a given U.
> >
> > TABLE [Todo]
> > [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> > [UID] [int] NULL ,
> > [PID] [int] NULL ,
> > [CID] [int] NULL ,
> > [dir] [Int] NOT NULL
> >
> > So for the given xml file above the contents of Todo is;
> >
> > row1=n,[thedate],1,NULL,NULL,"0"
> > row2=n+1,[thedate],1,1,NULL,"0"
> > row3=n+1,[thedate],1,1,1,"0"
> > row4=n+1,[thedate],1,1,2,"0"
> > row5=n+1,[thedate],1,1,3,"0"
> >
> > I hope that is clear and that you can help me.
> > Many thanks
> > Andy L
> >
> >
> >
> >
> >|||Ultradiv
Perhaps instead of using SQL Server ,you may write some VB.NET script to
deal with it.
"Ultradiv" <andy@.visualpurple.net> wrote in message
news:%23tkI7V4vEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Yeah it is complicated!
> Its fine with two queries and UNION ALL
> but adding the third part has proved very difficult
> Thanks for the reference Anthony, I'll read through to see if there is
> anything more than my MS book 'Programming Microsoft SQL Server 2000 with
> XML Second edition' and let you know
> Cheers,
> Andy
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:F890E101-9DE3-4F60-8714-71174E65A39E@.microsoft.com...
> > This is one of the more complicated dialects; however, check out the
> > following link--it has quite a bit of detailed syntax information.
> >
> >
>
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "Ultradiv" wrote:
> >
> > > Hi,
> > > I am having some difficulty extracting the xml that is required using
> FOR
> > > XML EXPLICIT and I am wondering if you can help me please?
> > >
> > > The target xml format is this;
> > >
> > > <?xml version="1.0"?>
> > > 
> > > 
> > > <c i="1238" t="1">
> > > <n>Test Phone</n>
> > > <a>1-544-552-8900</a>
> > > <f>0x0022</f>
> > > </c>
> > > <c i="1233" t="1">
> > > <n>Phone Tester</n>
> > > <a>1-788-111-42322</a>
> > > <f>0x0011</f>
> > > </c>
> > > <c i="1234" t="1">
> > > <n>Cell Tester</n>
> > > <a>1-445-255-3339</a>
> > > <f>0x0111</f>
> > > </c>
> > > 
> > > 
> > >
> > > With any number of P's
> > > each with any number of c's
> > > each c may or may not have 1 each of n, a & f
> > > or another way of looking at it- apart from PK's and FK's
> > > All NOT NULL's will always be present & all NULL values will not be
> > > represented in the resulting file
> > >
> > > The data is contained in 3 tables;
> > >
> > > TABLE [U]
> > > [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [sid] [varchar] (20) NOT NULL
> > >
> > > TABLE [P]
> > > [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> > > [UID] [int] NOT NULL, (FK)
> > > [num] [varchar] (20) NOT NULL,
> > > [f] [int] NULL,
> > > [x] [int] NULL,
> > > [d] [int] NULL,
> > > [t] [int] NOT NULL
> > >
> > >
> > > TABLE [c]
> > > [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [PID] [int] NOT NULL , (FK)
> > > [i] [varchar] (20) NOT NULL,
> > > [n] [varchar] (20) NULL,
> > > [a] [varchar] (20) NULL,
> > > [f] [varchar] (8) NULL
> > >
> > >
> > > I have a fourth table which contains pointers to the data that needs
> > > including for a given U.
> > >
> > > TABLE [Todo]
> > > [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> > > [UID] [int] NULL ,
> > > [PID] [int] NULL ,
> > > [CID] [int] NULL ,
> > > [dir] [Int] NOT NULL
> > >
> > > So for the given xml file above the contents of Todo is;
> > >
> > > row1=n,[thedate],1,NULL,NULL,"0"
> > > row2=n+1,[thedate],1,1,NULL,"0"
> > > row3=n+1,[thedate],1,1,1,"0"
> > > row4=n+1,[thedate],1,1,2,"0"
> > > row5=n+1,[thedate],1,1,3,"0"
> > >
> > > I hope that is clear and that you can help me.
> > > Many thanks
> > > Andy L
> > >
> > >
> > >
> > >
> > >
>|||This is how I did it & it works great!
Thanks alot,
Select Top 1 1 as Tag,
Null as Parent,
#Inter.SystemID as [U!1!sid],
#Inter.Direction as [U!1!dir],
Null as [P!2!num],
Null as [P!2!f],
Null as [P!2!x],
Null as [P!2!d],
Null as [P!2!t],
Null as [c!3!i],
Null as [c!3!t],
Null as [c!3!n!element],
Null as [c!3!a!element],
Null as [c!3!f!element]
FROM #Inter
UNION ALL
Select 2,
1,
#Inter.SystemID,
Null,
#Inter.PhoneNumber,
#Inter.AllottedTime,
#Inter.ExtraTime,
#Inter.ResetDate,
#Inter.Command,
NULL,
NULL,
NULL,
NULL,
NULL
FROM #Inter Where ContactRef is NULL
Union all
Select 3,
2,
NULL,
NULL,
#Inter.PhoneNumber,
NULL,
NULL,
NULL,
NULL,
#Inter.ContactRef,
#Inter.Command,
#Inter.ContactName,
#Inter.ContactAddress,
#Inter.ContactFlags
FROM #Inter Where (ContactRef is NOT NULL)
Order By [P!2!num],[c!3!i]
FOR XML EXPLICIT|||Hi Uri
I didn't want to use anything outside the 'SQL Server box' because the
server application that will be calling this sp is C++ and is already built
with the names of the sp's in it and I don't want to get the developer to do
any more work on it. Also I didn't fancy the idea of using templates or any
other 'referenced file means' to produce this xml coz then I have to write
other xsl stuff which I don't understand!
Once conquered FOR XML EXPLICIT seems the best solution really.
Andy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eKuqcA%23vEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Ultradiv
> Perhaps instead of using SQL Server ,you may write some VB.NET script to
> deal with it.

Friday, February 17, 2012

Extracting data as xml from 4 tables

No comments:

Post a Comment

Extracting Sql

Blog Archive

About Me