March 4, 2011 at 9:10 am
Hi,
One of the valued members at SQLServerCentral has helped me to produce the following T-SQL (Figure 1) which when run against my Database produces xml similar to the example shown (Figure 2).
However, when I run this code either as Direct input in an 'Execute SQL Task' or as a Stored Procedure (also within an 'Execute SQL Task') I get an 'XML document must have a top level element. Error processing resource' error.
WITH Unpivotted (ZIPCODEFROM ,ZIPCODETO,Value,Name) AS (
SELECT ZIPCODEFROM ,NULL,'start','ID'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,'Start','NAME'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,'1','RIGIDACC'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,'end','ID'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,'End','NAME'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,ZIPCODEFROM,'POSTCODE'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,ZIPCODETO,'POSTCODE'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
)
SELECT 'DEPOT' AS "@ObjectType",
(SELECT a.Name AS "@KeywordName",
a.Value AS "text()"
FROM Unpivotted a
WHERE a.ZIPCODEFROM=b.ZIPCODEFROM
FOR XML PATH('Value'), ROOT('Object'), TYPE)
FROM dbo.Mileage b
WHERE b.ZIPCODEFROM<>''
UNION ALL
SELECT 'CUST' AS "@ObjectType",
(SELECT a.Name AS "@KeywordName",
a.Value AS "text()"
FROM Unpivotted a
WHERE a.ZIPCODETO=b.ZIPCODETO
FOR XML PATH('Value'), ROOT('Object'), TYPE)
FROM dbo.Mileage b
WHERE b.ZIPCODETO<>''
FOR XML PATH('ObjectSequence'), ROOT('SupplierData'), TYPE;
Figure 1
<SupplierData>
<ObjectSequence ObjectType="DEPOT">
<Object>
<Value KeywordName="ID">start</Value>
<Value KeywordName="NAME">Start</Value>
<Value KeywordName="RIGIDACC">1</Value>
<Value KeywordName="POSTCODE">XX37 9</Value>
</Object>
</ObjectSequence>
<ObjectSequence ObjectType="CUST">
<Object>
<Value KeywordName="ID">end</Value>
<Value KeywordName="NAME">End</Value>
<Value KeywordName="POSTCODE">ZZ12 4</Value>
</Object>
</ObjectSequence>
</SupplierData>
Figure 2
The Source Output is '<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>'
Any ideas please?
Thanks in advance,
March 7, 2011 at 3:50 pm
I would like to help. Can you please post the DDL for your table and some DML to load it with some usable test data?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 3:27 am
Thanks,
I tried adding ‘SET NOCOUNT ON;’ to the Execute SQL Task 'Direct Input' but I’m still getting the same error. I have added some code, as you requested:
Mileage table
--DDL
USE [TESTDB]
GO
/****** Object: Table [dbo].[Mileage] Script Date: 03/08/2011 09:48:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Mileage](
[Id] [bigint] NOT NULL,
[ZipCodeFrom] [nvarchar](10) NOT NULL,
[MatrixType] [int] NOT NULL,
[Mileage] [numeric](28, 12) NOT NULL,
[ZipCodeTo] [nvarchar](10) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedTime] [int] NOT NULL,
[CreatedBy] [nvarchar](5) NOT NULL,
[CompanyID] [nvarchar](3) NOT NULL,
CONSTRAINT [PK_Mileage] PRIMARY KEY CLUSTERED
(
[CreatedDate] ASC,
[CreatedTime] ASC,
[CompanyID] ASC,
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--DML INSERT
INSERT INTO dbo.Mileage
VALUES(1,'XX37 9',2,'71.000000000000','ZZ12 4','2009-08-24 00:00:00',58014,'Blogg','ABC');
When debugging, (OnPostExecute) the result appears to be blank ''. I pass the result to a string variable (XMLOutput). When I debug XMLOutput, the value is:
'{<ROOT></ROOT>\r}'
Next, within a Script Task in the package, I use code to strip the 'ROOT' elements from the variable string.
Hope that is clearer,
March 9, 2011 at 10:28 am
Sorry this took so long...
I have a testbed setup and can see the XML value from your DB query within a script task...it looks OK and as you mentioned now has <ROOT> tags around it.
For the record I have two user variables setup at the package level as strings, xml_data and XMLOutput. I am storing the XML result from the Execute SQL Task in xml_data and am passing that to the script task in the ReadOnlyVariables collection. I am passing XMLOutput to the script task in the ReadWriteVariables collection. Here is my VB code in the script task:
Public Sub Main()
MsgBox("XML: " & Dts.Variables("xml_data").Value.ToString)
Dts.Variables("XMLOutput").Value = Dts.Variables("xml_data").Value.ToString
Dts.TaskResult = Dts.Results.Success
End Sub
I have a breakpoint set for the OnPostExecute event and the locals window shows that XMLOutput contains the same string as xml_data which is good...so I am getting my data through the script task.
This statement of yours has me confused:
I pass the result to a string variable (XMLOutput). When I debug XMLOutput, the value is:
'{<ROOT></ROOT>\r}'
I think I have an environment that mimics yours I just don't know exactly what you want to see out of the XMLOutput variable...can you please post your script task code and expected value of XMLOutput?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2011 at 6:24 am
Hi,
Firstly, thanks for your time and effort.
The problem still seems to be in the Execute SQL Task. I again tested at breakpoint at OnPostExecute and found that..
If I include the 'SET NOCOUNT ON;' statement then my variable value is: <ROOT></ROOT>\r (i.e. carriage return?)
If I don't, then my variable is:
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
Maybe its my object settings, which are..
General - Tab
Result = XML
ConnectionType = OLE DB
Connection = MyDB
SQLSourceType = Direct input
Statement = (As in the original posting)
ByPassPrepare = False
Result Set - Tab
Variable Name = User::XMLOutput
Result Name = 0
This is frustrating. The output expected is in my original posting.
March 10, 2011 at 8:21 am
Hi,
I managed to find a fix on the web. Basically, I changed the connection manager type to ADO.Net and it worked.
See link:
March 10, 2011 at 8:31 am
I guess I had gotten farther than you and missed the issue completely...I thought you were having trouble parsing the XML in the script task. ADO.NET is what I used...glad you arrived at a solution 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply