March 25, 2011 at 9:59 am
This are 2 questions I have. I am putting all the pertinant information in here so hopefully I can get help with one or both questions (questions at bottom of topic). I have googled around to different sites and can not find my answers so hope I can get some here.
I have created an SSIS package to create a file in XML format from a SQL Server DB.
I have a stored procedure that I call in the SSIS packaged which says:
SELECT
/*format for all Individuals*/
(SELECT
FirstName as "name/@first_name",
CASE WHEN MiddleName IS NOT NULL THEN MiddleName ELSE '' END as "name/@middle_name",
LastName as "name/@last_name",
Address1 as "address/address_line1",
City as "address/city",
State as "address/state",
Zip as "address/postal_code",
AcctNo as "account_number"
FROM CustomerInfo L2
WHERE L2.acctNo = L1.acctNo AND L1.Type = 'I'
FOR XML PATH(''), type),
/*format for all companies*/
(SELECT
'"?"' as "name/@first_name",
'' as "name/@middle_name",
'"?"' as "name/@last_name",
LastName as "company_data/company_name",
Address1 as "company_data/address/address_line1",
City as "company_data/address/city",
State as "company_data/address/state",
Zip as "company_data/address/postal_code",
AcctNo as "account_number"
FROM CustomerInfo L3
WHERE L3.acctNo = L1.acctNo AND L1.Type = 'C'
FOR XML PATH(''), type)
FROM CustomerInfo L1
FOR XML PATH('applicant'), ROOT('batchImport')
In my SSIS package, I have an 'Execute SQL Task' which runs the above stored procedure and returns as an XML Result Set and also a Script Task with the following VB.net code inside to create the xml file:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim XMLString As String = ""
XMLString = Dts.Variables("XMLContent").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
XMLString = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" + XMLString
GenerateXmlFile("C:\Results\Customers.xml", XMLString)
End Sub
Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)
Dim objStreamWriter As IO.StreamWriter
Try
objStreamWriter = New IO.StreamWriter(filePath)
objStreamWriter.Write(fileContents)
objStreamWriter.Close()
Catch Excep As Exception
MsgBox(Excep.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
This whole process runs fine and when I look at the xml file it creates, it produces the following which matches up to what I want:
<?xml version="1.0" encoding="UTF-8" ?>
- <batchImport>
- <applicant>
<name first_name="john" middle_name="A" last_name="doe" />
- <address>
<address_line1>150 wood st</address_line1>
<city>new york</city>
<state>NY</state>
<postal_code>11111</postal_code>
</address>
<account_number>111111</account_number>
</applicant>
- <applicant>
<name first_name=""?"" middle_name="" last_name=""?"" />
- <company_data>
<company_name>Johns Trucks</company_name>
- <address>
<address_line1>520 main st</address_line1>
<city>Cincinnati</city>
<state>OH</state>
<postal_code>04542</postal_code>
</address>
</company_data>
<account_number>11111</account_number>
</applicant>
- <applicant>
<name first_name=""?"" middle_name="" last_name=""?"" />
- <company_data>
<company_name>Mary's Flowers</company_name>
- <address>
<address_line1>13405 Main St</address_line1>
<city>Boston</city>
<state>MA</state>
<postal_code>02552</postal_code>
</address>
</company_data>
<account_number>112351</account_number>
</applicant>
- <applicant>
<name first_name="Mary" middle_name="" last_name="Smith" />
- <address>
<address_line1>90 Cross st</address_line1>
<city>Providence</city>
<state>RI</state>
<postal_code>52421</postal_code>
</address>
<account_number>669541</account_number>
</applicant>
</batchImport>
Questions
1. When I run my result in an XML Validator, it tells me that:
An error has been found!
The processing instruction target matching "[xX][mM][lL]" is not allowed.
I put a * to reference the error it indicates <?xml * version="1.0" encoding="UTF-8" ?>
From what I read it tells me that there is a space or something before the tag but I can't find it.
2. The specs that I am following for my xml file show a user name tag to be included:
<?xml version="1.0" encoding="UTF-8" ?>
- <batchImport>
- <userName>UserName</userName>
- <applicant>
Using my code that I provided above, how can I get that user name tag in that place. I've tried multiple ways and it will embed that tag in different areas in my result. I need it to be below the root node and above the main node.
Any help on my 2 questions would be greatly appreciated. If this needs to be in a different topic area, please let me know.
Thanks in advance
March 25, 2011 at 12:44 pm
Re: item 1
Is that your actual XML? If so I see two issues:
1. You have dashes prefixing some of your lines:
- <batchImport>
2. You have unescaped quotes in some attributes:
<name first_name=""?"" middle_name="" last_name=""?"" />
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2011 at 1:05 pm
Re: Item 2:
The idea is to move your ROOT tag out a level and add the username element before the rest of the stuff. I don't have your tables so I could not test but here is what it would look like cobbled together from the original example:
SELECT
-- add username
(SELECT 'someones_username' AS "username"
FOR
XML PATH(''),
TYPE
),
(
-------------------------------------------------------------------------------
--start: your original query (minus ROOT)
-------------------------------------------------------------------------------
SELECT
/*format for all Individuals*/
(SELECT FirstName AS "name/@first_name",
CASE WHEN MiddleName IS NOT NULL THEN MiddleName
ELSE ''
END AS "name/@middle_name",
LastName AS "name/@last_name",
Address1 AS "address/address_line1",
City AS "address/city",
State AS "address/state",
Zip AS "address/postal_code",
AcctNo AS "account_number"
FROM CustomerInfo L2
WHERE L2.acctNo = L1.acctNo
AND L1.Type = 'I'
FOR
XML PATH(''),
TYPE
),
/*format for all companies*/
(SELECT '"?"' AS "name/@first_name",
'' AS "name/@middle_name",
'"?"' AS "name/@last_name",
LastName AS "company_data/company_name",
Address1 AS "company_data/address/address_line1",
City AS "company_data/address/city",
State AS "company_data/address/state",
Zip AS "company_data/address/postal_code",
AcctNo AS "account_number"
FROM CustomerInfo L3
WHERE L3.acctNo = L1.acctNo
AND L1.Type = 'C'
FOR
XML PATH(''),
TYPE
)
FROM CustomerInfo L1
FOR XML PATH('applicant')
-------------------------------------------------------------------------------
-- end: your original query (minus ROOT)
-------------------------------------------------------------------------------
)
FOR XML PATH(''),
ROOT('batchImport') ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2011 at 2:27 pm
You fixed both my issues!!!
The first one was the
- in front of the tag. I was copying it from IE when I put it in the validator.
I appreciate your prompt response and help on this.
Thank you - Thank you
March 25, 2011 at 2:35 pm
You're welcome 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply