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

FOR XML Query

Hi Guys,
I am trying to understand the FOR XML EXPLICIT option and what I don't
understand is , how is the XML being generated , for example,
select 1 AS TAG,
NULL as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
select 2 AS TAG,
1 as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
both the above queries on Northwind returns 92 rows - When I union them a
(EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
does this happen ? What's the trick here ?
Thanks!
You only have 2 distinct rows specified:
1 null null null
2 1 null null
if you select some data from the tables by specifying some column names you
will get more rows in the result. UNION eliminates duplicates so you're
down to only two rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"pSm" <pSm@.discussions.microsoft.com> wrote in message
news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
> Hi Guys,
> I am trying to understand the FOR XML EXPLICIT option and what I
> don't
> understand is , how is the XML being generated , for example,
> ----
> select 1 AS TAG,
> NULL as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ----
> select 2 AS TAG,
> 1 as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> both the above queries on Northwind returns 92 rows - When I union them a
> (EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
> does this happen ? What's the trick here ?
> Thanks!
>
|||Also, note that for FOR XML, you should use UNION ALL that does not
eliminate duplicates.
There was an old article that Michael Conning and I write back in 99 that
explains the explicit mode (you should find it with a websearch). Or both
Books Online and the SQL Server 2005 whitepaper on MSDN about FOR XML should
be able to help.
HTH
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uGJCbTh9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> You only have 2 distinct rows specified:
> 1 null null null
> 2 1 null null
> if you select some data from the tables by specifying some column names
> you will get more rows in the result. UNION eliminates duplicates so
> you're down to only two rows.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "pSm" <pSm@.discussions.microsoft.com> wrote in message
> news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
>

FOR XML Query

Hi Guys,
I am trying to understand the FOR XML EXPLICIT option and what I don't
understand is , how is the XML being generated , for example,
----
select 1 AS TAG,
NULL as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
----
select 2 AS TAG,
1 as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
---
both the above queries on Northwind returns 92 rows - When I union them a
(EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
does this happen ? What's the trick here ?
Thanks!You only have 2 distinct rows specified:
1 null null null
2 1 null null
if you select some data from the tables by specifying some column names you
will get more rows in the result. UNION eliminates duplicates so you're
down to only two rows.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"pSm" <pSm@.discussions.microsoft.com> wrote in message
news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
> Hi Guys,
> I am trying to understand the FOR XML EXPLICIT option and what I
> don't
> understand is , how is the XML being generated , for example,
> ----
> select 1 AS TAG,
> NULL as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ----
> select 2 AS TAG,
> 1 as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ---
> both the above queries on Northwind returns 92 rows - When I union them a
> (EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
> does this happen ? What's the trick here ?
> Thanks!
>|||Also, note that for FOR XML, you should use UNION ALL that does not
eliminate duplicates.
There was an old article that Michael Conning and I write back in 99 that
explains the explicit mode (you should find it with a websearch). Or both
Books Online and the SQL Server 2005 whitepaper on MSDN about FOR XML should
be able to help.
HTH
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uGJCbTh9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> You only have 2 distinct rows specified:
> 1 null null null
> 2 1 null null
> if you select some data from the tables by specifying some column names
> you will get more rows in the result. UNION eliminates duplicates so
> you're down to only two rows.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "pSm" <pSm@.discussions.microsoft.com> wrote in message
> news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
>

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
>