hi,
i cant seem to import Microsoft.SqlServer.Dts.Pipeline.
What i only have is Microsoft.SqlServer.Dts.Runtime.
can you tell me how to have to this?
also, how can i access ComponentMetaData object? Is this part of Dts.Pipeline?
thanks.
- ranier
Hi
I've been banging my head against this one for a while. There are examples around but I can't seem to find anything that works (or find an explanation).
The problem: The xml document uses an imported common schema to reference common tags/objects (xmlns:cmn) but I can't seem to get openXML working with multiple namespaces. If I remove the primary namespace (emboldened in red) the SELECT returns as expected. Editing the xml document is not an option.
Here's (a simplified version) of my code...
--------------------------------------------------------------------
DECLARE @idoc INT
DECLARE @doc varchar (8000)
SET @doc ='
<?xml version="1.0" encoding="UTF-8"?>
<LHA_DATA xmlns="http://www.trs.co.uk/schema/DataShare.xsd" xmlns:cmn="http://www.trs.co.uk/schema/Common_ed.xsd">
<Manifest>
<cmn:senderRecipient>
<cmn:LACode>A0000</cmn:LACode>
<cmn:OfficeCode>0</cmn:OfficeCode>
</cmn:senderRecipient>
</Manifest>
</LHA_DATA>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<LHA_DATA xmlns:cmn="xmlns="http://www.trs.co.uk/schema/DataShare.xsd" http://www.trs.co.uk/schema/Common_ed.xsd" />'
SELECT *
FROM OPENXML (@idoc, 'LHA_DATA', 2)
WITH (LACode varchar(10) '//cmn:LACode',
OfficeCode varchar(10) '//cmn:OfficeCode')
EXEC sp_xml_removedocument @idoc
------------------------------------------------------------------------
Any help greatly received!!!!
I'm having trouble getting the namespaces correct in the XML that is generated by SQL. The XML consists of two parts: a header part with some context information and the payload part with the actual data. With my current SQL script, the namespace is also copied to the payload part.
Here are some DDL and DML scripts to generate two test tables and some test data:
Code:
CREATE TABLE dbo.context(
idintNOT NULLconstraint pk_dbo_context primary key,
namenvarchar(100)NOT NULL
)
INSERT INTO dbo.context(id, name) VALUES (1, 'Here comes some great context information.')
[Code] ....
This is my current FOR XML script that generates the XML:
Code:
;WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com')
SELECT
CEXT.name AS [Context/Name],
(SELECT
CONT.id AS [Content/Reference],
[Code] ....
This generates this XML (notice that the namespace is repetated in the <Questions> element):
Code:
<RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
<Request>
<Context>
<Name>Here comes some great context information.</Name>
[Code] ....
The XML should be like this:
Code:
<RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
<Request xmlns="">
<Context>
<Name>Here comes some great context information.</Name>
[Code] ....
The generated XML does not pass the XSD validation.
Hi eveyone, I'm reading about namespaces and schemas in MS SQL 2005, and that's exactly what I was looking for for the structure of my database.
I've a database and 4 different applications that will use it, and every application will create a new database every year, for example, i'd like to use the following schema:
MainDatabase.MyApplication.MyYear.MyTable
In MS SQL i've created my main schema using:
USE Mydatabase;
CREATE SCHEMA MyApplication
GO
Now I want to add sub-groups (in this example, I need to create Year2006, Year2007, and so on).
Even If i create those schemas, how can I "add" them inside the Salary main schema?
I've tryed with
ALTER SCHEMA MyApplication TRANSFER Year2006
but it doesn't work since Year2006 is not an object.
Thank you!
I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.
For example, if my XML is:
<o:object id="12345" xmlns:o="urn:some-urn"/>
and my desired XPATH is "/o:object/@id", I get the following error:
Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".
Of course, the XPATH "/object/@id" returns nothing as expected.
Is there a way around this?
I am trying to select data from a column that has xml data in it. I have tried all I can think of, but the results keep coming back as NULL. Not sure why but I pasted the top of the xml document so show what I am trying to do.
- <ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
- <ST>
<ST01_TransactionSetIdentifierCode>837</ST01_TransactionSetIdentifierCode>
<ST02_TransactionSetControlNumber>1001</ST02_TransactionSetControlNumber>
<ST03_ImplementationGuideVersionName>005010X222A1</ST03_ImplementationGuideVersionName>
</ST>
And the query...
WITH XMLNAMESPACES('[ns0]' AS ns)
SELECT sourceclaimxml.value('(/ST01_TransactionSetIdentifierCode)[2]', 'varchar(300)') XMLValue
FROM xclaim_audit_xml
CROSS APPLY sourceclaimxml.nodes('//ST') as P(nref)
WHERE edixid='2323111'
I have an XML document that I am trying to use within a data flow (XML Source)--the problem is that I keep getting an error message stating that the XML document has multiple namespaces and therefore, SSIS will not create the XSD for me. If I take out the second namespace, I am able to successfully get the task to execute, but this XML is created with 2 namespaces and there is no way to get around this (I cannot get the report server to change these parameters)--my question is: know a way to get SSIS to handle multiple namespaces so that I can process this XML document and extract the necessary data elements from it.
View 11 Replies View Related