Tuesday, March 27, 2012
Concatanete new line
I need to store data in XML format in the database. When the data is retrieved and printed onto a file, say, it should be automatically printed in a file format i.e. each seperate line on a new line. How can this be done? Can we append a newline character to each line before storing it as a record in the database?/If so, how can this be done?
Thanks in advanceYou can add a Carriage Return and Line Feed to a string as follows:
'your string' + CHAR(13) + CHAR(10)sqlsql
Tuesday, March 20, 2012
compression and the Primary XML index
Is SS08 compression going to help me with my XML. The Primary index is really just a table it appears, so I was hoping to compress that ... plesae?
It is definitely one of the areas that we are considering.
Thanks,
Thursday, March 8, 2012
Complex XML Parsing/Shredding
Hi,
I have generated a sample XML document from the XSD file using XMLSpy and used these in XML Source stage.
While I was parsing/Shredding the XML file and to write to sequential files I got the following error message.
The XML Source Adapter does not support mixed content model on Complex Types.
Additional Information:
Pipeline component has returned HRESULT error code )xC02092A1 from a method call. [Microsoft.SqlServer.DTSPipelineWrap]
Can anybody give a sample code with Complex XML type.
--
Find attached the XSD format. This is FYI.
Not sure I understand the question.
Empty, simple and element-only content models for complex types are supported, but mixed content is not.
Are you asking for a sample of a schema that has one of those content models?
|||Are you able to get my XSD schema attached in my previous question? If not, could you please tell me how to send you the XSD schema
My schema content will be changed based on the instrument type.
For example,
<instrument type='Equity'>
<equityrelatedcolumns>value</equityrelatecolumns>
<pricinglist>
<price1/>
<price2/>
...
...
...
<pricen/>
</pricinglist>
</instrument>
<instrument type='Bond'>
<bondrelatedcolumns>value</bondrelatedcolumns>
<pricinglist>
<price1/>
<price2/>
...
...
...
<pricen/>
</pricinglist>
</instrument>
Current SSIS support for loading XML does not support Complex Types with mixed content models. I looked at the schema provided and noticed several complex types with mixed="true" defined.
However, based on the XML sample provided, it is unclear to me why this is required since known of the element instances contained any non-element content. This would lead me to believe that at least fot the sample provided, you could change the content model on the ComplexTypes to not be mixed to work around the limitation of SSIS.
If this is not feasible, then you will have to preprocess te instance documents with an XSLT to turn the non-element content for the ComplexTypes with mixed content into element conent.
Hope that helps -
Andy
|||Thanks Andy. The XSD file is the correct one. Our original XML file will contain mixed content type only.
We generated sample XML file through XMLSPY and specified NULL for all the contents. That is the reason the XML file looks like simple content.
Hope SSIS next version might look into this issue.
Wednesday, March 7, 2012
Complex Schema problem
root node it works fine. But if I try to map the subelement with the database
table i.e the root node is not mapped to any database table than it throws
exception.
Can anyone elaborate.
Also, If someone has a good tutorial or example regarding the complex schema
mapping please refer it to me.
Sounds like you want to look at using is-mapped="false", but you didn't give
enough detail for me to be sure what you're doing. You can only have one
"root" node so mapping it at all should typically throw an error unless
you're using limit-field/limit-value to ensure you have only one row
returning to map. If that doesn't help, be sure to reply with the exception
and a copy of the schema throwing it.
rainabba
"Ahsan" <Ahsan@.discussions.microsoft.com> wrote in message
news:9640E9E3-78A8-4CFB-8388-5377CC81A283@.microsoft.com...
> I am trying to map a xml schema to a database. If I map the table with the
> root node it works fine. But if I try to map the subelement with the
database
> table i.e the root node is not mapped to any database table than it throws
> exception.
> Can anyone elaborate.
> Also, If someone has a good tutorial or example regarding the complex
schema
> mapping please refer it to me.
|||Ok, the mapping schema is as under:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ht="http://www.w3.org/1999/xhtml" xmlns:cus="http://localhost/"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
targetNamespace="http://localhost/" elementFormDefault="qualified"
attributeFormDefault="unqualified" >
<xs:element name="template">
<xs:complexType>
<xs:sequence>
<xs:element name="PatientDetails1" type="cus:FullNameBlock"
sql:relation="PatientDetails"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="FullNameBlock">
<xs:sequence>
<xs:element name="Name" type="cus:NameBlock" sql:relation="PatientName">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="PatientDetails" parent-key="PDName"
child="PatientName" child-key="ID" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="NameBlock">
<xs:sequence>
<xs:element name="FirstName" type="xs:string" sql:field="PDNFirstName" />
<xs:element name="LastName" type="xs:string" sql:field="PDNLastName" />
</xs:sequence>
<xs:attribute name="id" type="xs:ID"/>
</xs:complexType>
</xs:schema>
I am trying to xpath query the above schema using the following code:
try{
SqlXmlCommand cmd;
FileStream f;
string conString = "Provider=SQLOLEDB;Data
Source='(local)';database=TestRep; Integrated Security=SSPI";
cmd = new SqlXmlCommand(conString);
cmd.Namespaces = "xmlns:x='http://localhost/'";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.CommandText = "x:PatientDetails1";
cmd.SchemaPath = "F:\\Testing\\XML\\CustomSchema\\CustomSchema4.xsd ";
f = new FileStream("F:\\Testing\\XML\\CustomList3.xml", FileMode.Create);
cmd.ExecuteToStream(f);
f.Close() ;
}
catch(Exception ex)
{
string temp = ex.Message;
MessageBox.Show(temp);
}
}
when I run the above mentioned code it throws the following exception:
SqlXmlException
Message="Schema: relationship expected on 'PatientDetails1'."
"Rainabba" wrote:
[vbcol=seagreen]
> Sounds like you want to look at using is-mapped="false", but you didn't give
> enough detail for me to be sure what you're doing. You can only have one
> "root" node so mapping it at all should typically throw an error unless
> you're using limit-field/limit-value to ensure you have only one row
> returning to map. If that doesn't help, be sure to reply with the exception
> and a copy of the schema throwing it.
> rainabba
> "Ahsan" <Ahsan@.discussions.microsoft.com> wrote in message
> news:9640E9E3-78A8-4CFB-8388-5377CC81A283@.microsoft.com...
> database
> schema
>
|||Could you please try again with putting sql:is-constant='1' on the root
element (the element named as 'template' in your below schema) you don't
want to map?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ahsan" <Ahsan@.discussions.microsoft.com> wrote in message
news:A2C7C59D-5C67-4B9D-AF8B-6BF11FBA0C3B@.microsoft.com...[vbcol=seagreen]
> Ok, the mapping schema is as under:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:ht="http://www.w3.org/1999/xhtml" xmlns:cus="http://localhost/"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> targetNamespace="http://localhost/" elementFormDefault="qualified"
> attributeFormDefault="unqualified" >
> <xs:element name="template">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="PatientDetails1" type="cus:FullNameBlock"
> sql:relation="PatientDetails"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:complexType name="FullNameBlock">
> <xs:sequence>
> <xs:element name="Name" type="cus:NameBlock" sql:relation="PatientName">
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship parent="PatientDetails" parent-key="PDName"
> child="PatientName" child-key="ID" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="NameBlock">
> <xs:sequence>
> <xs:element name="FirstName" type="xs:string" sql:field="PDNFirstName" />
> <xs:element name="LastName" type="xs:string" sql:field="PDNLastName" />
> </xs:sequence>
> <xs:attribute name="id" type="xs:ID"/>
> </xs:complexType>
> </xs:schema>
> I am trying to xpath query the above schema using the following code:
>
> try{
> SqlXmlCommand cmd;
> FileStream f;
> string conString = "Provider=SQLOLEDB;Data
> Source='(local)';database=TestRep; Integrated Security=SSPI";
> cmd = new SqlXmlCommand(conString);
> cmd.Namespaces = "xmlns:x='http://localhost/'";
> cmd.CommandType = SqlXmlCommandType.XPath;
> cmd.CommandText = "x:PatientDetails1";
> cmd.SchemaPath = "F:\\Testing\\XML\\CustomSchema\\CustomSchema4.xsd ";
> f = new FileStream("F:\\Testing\\XML\\CustomList3.xml", FileMode.Create);
> cmd.ExecuteToStream(f);
> f.Close() ;
> }
> catch(Exception ex)
> {
> string temp = ex.Message;
> MessageBox.Show(temp);
> }
> }
> when I run the above mentioned code it throws the following exception:
> SqlXmlException
> Message="Schema: relationship expected on 'PatientDetails1'."
> "Rainabba" wrote:
>
>
Sunday, February 19, 2012
complete VB.NET SQL & XML example
query from sql server 2000 into xml and loading that xml back to server?
thanks!
I need the same thing what you. If you will be have something please send
me: ljag@.wp.pl
Thanks
Uytkownik "dotnet" <bernard@.form.hr> napisa w wiadomoci
news:clqtob$98i$1@.ls219.htnet.hr...
> is there anybody who can send me a complete vb.net example for saving a
> query from sql server 2000 into xml and loading that xml back to server?
> thanks!
>
compiler -- newbie q
I am very new to xml. i have downloaded some sample data from MS and there
is file in there called: CustService.sln
I am required to comile this file. How do I compile it? I dont have a
comiler on my computer.
Thanks,
A .sln file is a Visual Studio .NET solution file. I suspect the example
assumes you have Visual Studio installed.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Hasan Kachal" <kachal@.nospam.com> wrote in message
news:Os4q60LTEHA.2544@.TK2MSFTNGP10.phx.gbl...
Hi,
I am very new to xml. i have downloaded some sample data from MS and there
is file in there called: CustService.sln
I am required to comile this file. How do I compile it? I dont have a
comiler on my computer.
Thanks,
Friday, February 10, 2012
Comparing two SSIS Packages?
I'd like to know how people out there are comparing ("diffing") their DTSX files.
Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.
ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"
I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.
I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.
So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.
Thanks in advance!
No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
[Microsoft follow-up]
|||Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...
Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...
To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...
|||Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper
Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.
|||
jwelch wrote:
Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper
Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.
Thank you!
This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.
|||
jwelch wrote:
Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper
Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.
So... what are you using to compare packages, today?
|||I try not to
Usually I use a text difference tool and deal with the headaches of elements being in different order.
|||
jwelch wrote:
I try not to
Usually I use a text difference tool and deal with the headaches of elements being in different order.
Well, it's good to know that great minds do indeed think alike.
Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.