| Author |
SQL adapter returning multiple rows
|
|
| Nabeel Prior 2004-12-22, 7:46 am |
| Brian, do you need to break the data received from SQL into seperate files?
If not, then you would not need to worry about envelopes, and you should
then be able to process the data through BizTalk in its entirety and produce
a flat file through the send pipeline for the entire set.
If you do want to utilise envelopes and you want to split the message
received from SQL, you need to set the Body XPath to the root node, as you
want to split on the dbo.SMSLog node. Think of the Body XPath as the XPath
that will yield a Node whose children will be split into seperate messages.
--
Kind Regards,
Nabeel Prior
Web: http://www.brainjar.co.za
Blog: http://www.brainjar.co.za/blog.aspx
"Brian C" <brian.cesafsky@midwestwireless.com> wrote in message
news:uiNbJv25EHA.2016@TK2MSFTNGP15.phx.gbl...
>I have a receive port that calls a stored proc that will return multiple
>rows. I need the output to be a flat file. I have everything working
>except I only get the first row that is returned from the stored proc (only
>1 row of output in the flat file). I've gone through an example of
>creating an envelope schema (the schema was first generated through the SQL
>adapter wizard), but I have issues. I'm not sure how to set the 'Body
>Xpath' and the 'Data Structure Type' properties. The 'Data Structure Type'
>property was set through the SQL adapter wizard, and I'm not sure if I
>should change it.
>
> I've attached the schema.
>
>
> Thanks in advace, Brian
>
>
>
| |
| Brian C 2004-12-22, 5:52 pm |
| I don't want to break the data into separate files. I am not using an
envelope schema, but I only get the 1 row of data even though there are
several rows coming from the SQL statement. I have a map that I use to push
that data from the SQL generated schema to the flat file schema... I don't
know what I'm doing wrong... Any help would be appreciated.
| |
| Richard Blewett [DevelopMentor] 2004-12-22, 5:52 pm |
| Have you hooked a file adapter based send port using the passthrough pipeline direct to the receive port using a filter just to ensure that you really are only getting one row and its not the map that just makes it appear that way?
Regards
Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk
I don't want to break the data into separate files. I am not using an
envelope schema, but I only get the 1 row of data even though there are
several rows coming from the SQL statement. I have a map that I use to push
that data from the SQL generated schema to the flat file schema... I don't
know what I'm doing wrong... Any help would be appreciated.
| |
| Brian C 2004-12-22, 5:52 pm |
| Good idea, I just tried that and I am still just getting the 1 row. What
else can I check?
Thanks...
Brian
"Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
message news:%23poXuXE6EHA.3504@TK2MSFTNGP12.phx.gbl...
> Have you hooked a file adapter based send port using the passthrough
> pipeline direct to the receive port using a filter just to ensure that you
> really are only getting one row and its not the map that just makes it
> appear that way?
>
> Regards
>
> Richard Blewett - DevelopMentor
> http://www.dotnetconsult.co.uk/weblog
> http://www.dotnetconsult.co.uk
>
> I don't want to break the data into separate files. I am not using an
> envelope schema, but I only get the 1 row of data even though there are
> several rows coming from the SQL statement. I have a map that I use to
> push
> that data from the SQL generated schema to the flat file schema... I don't
> know what I'm doing wrong... Any help would be appreciated.
>
| |
| Brian C 2004-12-22, 5:52 pm |
| I take that back, somehow the map and pipeling I'm using makes me only get 1
record. If I use a passthrough pipeline and just let the XML go to my
output directory, I see 2 records (my query returns 2 records). Can you
help me find out what I'm doing wrong?
Thanks!
"Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
message news:%23poXuXE6EHA.3504@TK2MSFTNGP12.phx.gbl...
> Have you hooked a file adapter based send port using the passthrough
> pipeline direct to the receive port using a filter just to ensure that you
> really are only getting one row and its not the map that just makes it
> appear that way?
>
> Regards
>
> Richard Blewett - DevelopMentor
> http://www.dotnetconsult.co.uk/weblog
> http://www.dotnetconsult.co.uk
>
> I don't want to break the data into separate files. I am not using an
> envelope schema, but I only get the 1 row of data even though there are
> several rows coming from the SQL statement. I have a map that I use to
> push
> that data from the SQL generated schema to the flat file schema... I don't
> know what I'm doing wrong... Any help would be appreciated.
>
| |
| Richard Blewett [DevelopMentor] 2004-12-22, 5:52 pm |
| Do you have a custom pipeline or are you using one of the built in ones? And do you have something like MinOccurs=1 MaxOccurs=1 in your target map?
Regards
Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk
I take that back, somehow the map and pipeling I'm using makes me only get 1
record. If I use a passthrough pipeline and just let the XML go to my
output directory, I see 2 records (my query returns 2 records). Can you
help me find out what I'm doing wrong?
Thanks!
"Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
message news:%23poXuXE6EHA.3504@TK2MSFTNGP12.phx.gbl...
> Have you hooked a file adapter based send port using the passthrough
> pipeline direct to the receive port using a filter just to ensure that you
> really are only getting one row and its not the map that just makes it
> appear that way?
| |
| Brian C 2004-12-22, 5:52 pm |
| I'm using a built in send pipeline
"Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
message news:eGlvtOF6EHA.2032@tk2msftngp13.phx.gbl...
> Do you have a custom pipeline or are you using one of the built in ones?
> And do you have something like MinOccurs=1 MaxOccurs=1 in your target map?
>
> Regards
>
> Richard Blewett - DevelopMentor
> http://www.dotnetconsult.co.uk/weblog
> http://www.dotnetconsult.co.uk
>
> I take that back, somehow the map and pipeling I'm using makes me only
> get 1
> record. If I use a passthrough pipeline and just let the XML go to my
> output directory, I see 2 records (my query returns 2 records). Can you
> help me find out what I'm doing wrong?
>
> Thanks!
>
>
>
> "Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
> message news:%23poXuXE6EHA.3504@TK2MSFTNGP12.phx.gbl...
>
| |
|
|
| Brian C 2004-12-22, 5:52 pm |
| ooohhh,ooohhh... I almost got it now... I get both records, but I need to
insert a CRLF some how. I had my target schema defined wrong... When you
talked about the MinOccurs=1 MaxOccurs=1 in my target map, I went looking
and found that I didn't have my schema set up correctly...
Thanks for all your help!
P.S. how do insert a CRLF?
"Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
message news:%23FDjXmF6EHA.208@TK2MSFTNGP12.phx.gbl...
> Ummm, which one? XmlTransmit or PassthruTransmit?
>
> And can you post your target schema (or at least the relevant bits)?
>
> Regards
>
> Richard Blewett - DevelopMentor
> http://www.dotnetconsult.co.uk/weblog
> http://www.dotnetconsult.co.uk
>
> I'm using a built in send pipeline
>
>
| |
| Richard Blewett [DevelopMentor] 2004-12-22, 5:52 pm |
| Have you tried setting the "Indented" property of the grid to "Yes"?
Regards
> Richard Blewett - DevelopMentor
> http://www.dotnetconsult.co.uk/weblog
> http://www.dotnetconsult.co.uk
ooohhh,ooohhh... I almost got it now... I get both records, but I need to
insert a CRLF some how. I had my target schema defined wrong... When you
talked about the MinOccurs=1 MaxOccurs=1 in my target map, I went looking
and found that I didn't have my schema set up correctly...
Thanks for all your help!
P.S. how do insert a CRLF?
| |
| Nabeel Prior 2004-12-23, 2:46 am |
| Are you wanting to seperate the 2 records with a CRLF? If so, set the Child
Delimiter type to hex, Child Delimiter value to "0X0D 0X0D" on the root
node, and set the Child Order to Infix.
--
Kind Regards,
Nabeel Prior
Web: http://www.brainjar.co.za
Blog: http://www.brainjar.co.za/blog.aspx
"Brian C" <brian.cesafsky@midwestwireless.com> wrote in message
news:%23QCzKDG6EHA.828@TK2MSFTNGP14.phx.gbl...
> ooohhh,ooohhh... I almost got it now... I get both records, but I need to
> insert a CRLF some how. I had my target schema defined wrong... When you
> talked about the MinOccurs=1 MaxOccurs=1 in my target map, I went looking
> and found that I didn't have my schema set up correctly...
>
> Thanks for all your help!
>
> P.S. how do insert a CRLF?
>
>
> "Richard Blewett [DevelopMentor]" <richardb@NOSPAMdevelop.com> wrote in
> message news:%23FDjXmF6EHA.208@TK2MSFTNGP12.phx.gbl...
>
>
| |
| Nabeel Prior 2004-12-23, 7:46 am |
| Sorry - delimiter should be 0X0D 0X0A
--
Kind Regards,
Nabeel Prior
Web: http://www.brainjar.co.za
Blog: http://www.brainjar.co.za/blog.aspx
"Nabeel Prior" <nowhere@somewhere.com> wrote in message
news:ueyg7eM6EHA.3236@TK2MSFTNGP15.phx.gbl...
> Are you wanting to seperate the 2 records with a CRLF? If so, set the
> Child Delimiter type to hex, Child Delimiter value to "0X0D 0X0D" on the
> root node, and set the Child Order to Infix.
>
> --
>
> Kind Regards,
>
> Nabeel Prior
> Web: http://www.brainjar.co.za
> Blog: http://www.brainjar.co.za/blog.aspx
>
>
> "Brian C" <brian.cesafsky@midwestwireless.com> wrote in message
> news:%23QCzKDG6EHA.828@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Brian C 2004-12-23, 5:51 pm |
| Thanks Everyone for all the great help... I now have what I need.
Happy Holidays!
Brian
"Nabeel Prior" <nowhere@somewhere.com> wrote in message
news:%23MXDw9N6EHA.2804@TK2MSFTNGP15.phx.gbl...
> Sorry - delimiter should be 0X0D 0X0A
>
> --
>
> Kind Regards,
>
> Nabeel Prior
> Web: http://www.brainjar.co.za
> Blog: http://www.brainjar.co.za/blog.aspx
>
>
> "Nabeel Prior" <nowhere@somewhere.com> wrote in message
> news:ueyg7eM6EHA.3236@TK2MSFTNGP15.phx.gbl...
>
>
|
|
|
|