Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

for xml explicit or xsl

I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>

FOR XML EXPLICIT and namespaces

I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
However, then I cannot create a client proxy for my web service.
If I specify a (matching) targetNamespace in the web service description and the xsd file then I can create a proxy. However, at this point the XML produced by the SQL FOR XML EXPLICIT no longer validates.
I imagine this will be fixed if I specifiy a target namespace for my FOR XML EXPLICIT query (that is the root element should have a targetNamespace attribute).
I cannot see how to do this. There is sem reference to templates, but I believe these are use from http and I can't see any logical way to embed these in my SPROCS or ExecuteXmlReader calls.
Iain
"Iain" wrote:

> I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
> I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
...
Well, I found somewhere someone putting the xmlns in as a separate column in the select query. So ...
'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
and equivalent nulls in all the union locations.
This resulted in a query that worked sort of. Now for some reason it was rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a <DeliveryAddress> element a couple of nests down.
I fixed this by changing the elements in the element to attributes.
I don't know why this worked.
Iain
|||Adding xmlns or xmlns:foo (and then using !foo:bar) columns as you do are
the way to add namespaces in FOR XML currently (note that the PATH mode in
Beta2 will not support this and I am working on getting a better solution
for at least the PATH mode made available in Beta3).
If you use xmlns without specifying a prefix, all descending elements will
be in that namespace (unless another xmlns overwrites it). If your schema
does not allow those elements to be in that namespace, you may have a
problem.
Attributes on the other hand are never part of the default namespace. They
belong to the element unless an explicit namespace prefix has been
associated.
So this difference may account for what you see.
HTH
Michael
"Iain" <Iain@.discussions.microsoft.com> wrote in message
news:4B201E22-E0AF-4B12-9D98-E8B2DE3BD143@.microsoft.com...
> "Iain" wrote:
> ...
> Well, I found somewhere someone putting the xmlns in as a separate column
> in the select query. So ...
> 'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
> and equivalent nulls in all the union locations.
> This resulted in a query that worked sort of. Now for some reason it was
> rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a
> <DeliveryAddress> element a couple of nests down.
> I fixed this by changing the elements in the element to attributes.
> I don't know why this worked.
> Iain
>