Combining XML Files using SSIS or T-SQL etc.

Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>
What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:

> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>
|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...[vbcol=seagreen]
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:

Combining XML Files

I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.

So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>

and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>

Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?

JAson

Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?

|||

sorry, I meant that there would be more than 1 filed in the details file. So,

<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>

The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.

|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.

Combining XML

Assume I have the following, in T-SQL:

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

I want to combine these, into the following, and return the results typed as XML:

<results>

<first></first>

<second></second>

</results>

I can't seem to find any way to do this - other than converting them to a string, and concatinating them and then typing the results as XML. Any ideas?

Thanks,

Scott

One way to do this is to use 'FOR XML PATH'

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

select @.xml1 , @.xml2
FOR XML PATH ('results'), type

The section 'Constructing XML Using FOR XML ' in BOL has more information about the different FOR XML capabilities.

Thanks

Babu

|||

Beautiful!!!

Thank you Babu!

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!
In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>
|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Colour coding the RDL XML in vs2005

Hi.
Does any one know how to get vs2005 to colour code rdl apart from
renaming the file xml?
Some times one just needs to check the rdl file, and looking at it in
B&W isn't easy to visually scan.
Thanks.On Aug 4, 1:49 am, Ray Proffitt <ray...@.gmail.com> wrote:
> Hi.
> Does any one know how to get vs2005 to colour code rdl apart from
> renaming the file xml?
> Some times one just needs to check the rdl file, and looking at it in
> B&W isn't easy to visually scan.
> Thanks.
A good software to do this with is Notepad++ (open source): its a text
editor that will color code based on your language selection (i.e.,
XML, C#, HTML, etc). You can download it at: http://notepad-plus.sourceforge.net/uk/site.htm
It works well for this sort of thing.
Regards
Enrique Martinez
Sr. Software Consultant|||I've been using notepad++ for years. Problem is notepad++ doesn't have
the Report design view, and I frequently change to see the code, cos
sometimes I just need to...
VS2005 will colour code the XML too, just not when it has an RDL
extension, I think it's MS just trying to mother us too much.
I figured there might be a reg setting I could change.
I've tried a couple in here:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\Text Editor\RDL
Expression
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\Languages
\Language Services\RDL Expression
On Aug 5, 9:26 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 4, 1:49 am, Ray Proffitt <ray...@.gmail.com> wrote:
> > Hi.
> > Does any one know how to get vs2005 to colour code rdl apart from
> > renaming the file xml?
> > Some times one just needs to check the rdl file, and looking at it in
> > B&W isn't easy to visually scan.
> > Thanks.
> A good software to do this with is Notepad++ (open source): its a text
> editor that will color code based on your language selection (i.e.,
> XML, C#, HTML, etc). You can download it at:http://notepad-plus.sourceforge.net/uk/site.htm
> It works well for this sort of thing.
> Regards
> Enrique Martinez
> Sr. Software Consultant

Color-code and folding RDL?

Since the RDL is just XML, I would like VS2003 to color-code and allow
folding, just as it does with XML.
Does anyone know how to invoke this functionality? It should be very
simple.
Thanks,
Daniel WilliamsIn the File Open dialog, use the Open With option to open the RDL file using
"HTML/XML Editor".
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel Williams" <daniel@.aniworld.com> wrote in message
news:ObaM0TZ%23EHA.4028@.TK2MSFTNGP15.phx.gbl...
>
> Since the RDL is just XML, I would like VS2003 to color-code and allow
> folding, just as it does with XML.
> Does anyone know how to invoke this functionality? It should be very
> simple.
> Thanks,
> Daniel Williams
>