sql rowcount: xml

Showing posts with label xml. Show all posts

Tuesday, March 20, 2012

[XML Task] Error: An error occurred with the following error message: There are multiple root el

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

Kevin Rodgers wrote:

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!

Well of course as soon as I posted that I found an obvious bug: the xsl:stylesheet start tag should not be an empty element tag. But fixing that to:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

leads to an even more obscure error message:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

|||

Kevin Rodgers wrote:

Kevin Rodgers wrote:
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!
Well of course as soon as I posted that I found an obvious bug: the <xsl:stylesheet> start tag should not be an empty element tag. But fixing that to:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
leads to an even more obscure error message:
[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

Sorry to follow up again to my own question, but when I tried to run the transformation via Saxon (the free, open-source, fully compliant XSLT processor) it helpfully reported:

Warning: Running an XSLT 1.0 stylesheet with an XSLT 2.0 processor

And changing the xsl:stylesheet start tag to:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

did indeed allow SSIS to execute the XML task succesfully. But(!) the stylesheet did not work correctly. With Saxon, it correctly adds ihs_cats_seq attributes to each kw element, e.g.

<keywords><kw ihs_cats_seq="1">AERODYNAMICS</kw><kw ihs_cats_seq="2">AMENDMENT</kw><kw ihs_cats_seq="3">LOCATION</kw><kw ihs_cats_seq="4">ORGANISATION</kw><kw ihs_cats_seq="5">RECORD</kw><kw ihs_cats_seq="6">SCHEDULE</kw><kw ihs_cats_seq="7">SERIES</kw></keywords>

But SSIS adds ihs_cats_seq="" to each kw start-tag. Is there a known bug with the position() XPath function?

|||

I was able to get an answer in a different forum:

http://forums.microsoft.com/msdn/showpost.aspx?postid=654370

[XML Task] Error: An error occurred with the following error message: There are multiple roo

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

Kevin Rodgers wrote:

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!

Well of course as soon as I posted that I found an obvious bug: the xsl:stylesheet start tag should not be an empty element tag. But fixing that to:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

leads to an even more obscure error message:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

|||

Kevin Rodgers wrote:

Kevin Rodgers wrote:
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!
Well of course as soon as I posted that I found an obvious bug: the <xsl:stylesheet> start tag should not be an empty element tag. But fixing that to:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
leads to an even more obscure error message:
[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

Sorry to follow up again to my own question, but when I tried to run the transformation via Saxon (the free, open-source, fully compliant XSLT processor) it helpfully reported:

Warning: Running an XSLT 1.0 stylesheet with an XSLT 2.0 processor

And changing the xsl:stylesheet start tag to:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

did indeed allow SSIS to execute the XML task succesfully. But(!) the stylesheet did not work correctly. With Saxon, it correctly adds ihs_cats_seq attributes to each kw element, e.g.

But SSIS adds ihs_cats_seq="" to each kw start-tag. Is there a known bug with the position() XPath function?

|||

I was able to get an answer in a different forum:

http://forums.microsoft.com/msdn/showpost.aspx?postid=654370

sql

Sunday, March 11, 2012

[SQL 2005] XQuery xs:NOTATION

I thought I read in BOL that SQL 2K5's XQuery implementation does not
support the XML Schema xs:NOTATION datatype; but I'm having a hard time
finding that page again. I was able to locate a page in BOL
(http://msdn2.microsoft.com/en-us/library/ms191231.aspx) that indicates type
casting to/from xs:NOTATION is not supported, but need to know is
xs:NOTATION actually supported at all? Any references to this information
(one way or the other) is appreciated.
Thanks!Hi Mike
On the page you posted
http://msdn2.microsoft.com/en-us/library/ms191231.aspx :
"xs:NOTATION and the fully ordered subtypes of duration,
xdt:yearMonthDuration and xdt:dayTimeDuration, are not supported. As a
result, casting to or from these types is not supported."
On http://msdn2.microsoft.com/pt-br/library/ms189547.aspx
"Constructors are supported for base and derived atomic XSD types. However,
the subtypes of xs:duration, which includes xdt:yearMonthDuration and
xdt:dayTimeDuration, and xs:QName, xs:NMTOKEN, and xs:NOTATION are not
supported. User-defined atomic types that are available in the associated
schema collections are also available, provided they are directly or
indirectly derived from the following types."
John
"Mike C#" wrote:
> I thought I read in BOL that SQL 2K5's XQuery implementation does not
> support the XML Schema xs:NOTATION datatype; but I'm having a hard time
> finding that page again. I was able to locate a page in BOL
> (http://msdn2.microsoft.com/en-us/library/ms191231.aspx) that indicates type
> casting to/from xs:NOTATION is not supported, but need to know is
> xs:NOTATION actually supported at all? Any references to this information
> (one way or the other) is appreciated.
> Thanks!
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A04FBA1C-8FB5-4461-83D3-201B858C672F@.microsoft.com...
> Hi Mike
> On the page you posted
> http://msdn2.microsoft.com/en-us/library/ms191231.aspx :
> "xs:NOTATION and the fully ordered subtypes of duration,
> xdt:yearMonthDuration and xdt:dayTimeDuration, are not supported. As a
> result, casting to or from these types is not supported."
Thanks, that seems to indicate xs:NOTATION is not supported at all. The
fact that xs:NOTATION is included in the chart at the top of that page seems
to contradict that wording though...
> On http://msdn2.microsoft.com/pt-br/library/ms189547.aspx
> "Constructors are supported for base and derived atomic XSD types.
> However,
> the subtypes of xs:duration, which includes xdt:yearMonthDuration and
> xdt:dayTimeDuration, and xs:QName, xs:NMTOKEN, and xs:NOTATION are not
> supported. User-defined atomic types that are available in the associated
> schema collections are also available, provided they are directly or
> indirectly derived from the following types."
Here's where the confusion comes in - xs:NOTATION is either supported (as a
virtual base type, as indicated on the chart on the first page and alluded
to in the quote from the second page), or it's not supported at all (as
indicated by the wording on the first page).
I assume the statement from the second page doesn't really indicate that
xs:NOTATION itself is not supported, but rather that an xs:NOTATION
constructor is not supported. That's based on the fact that xs:QName *is*
supported (though no constructor), and it's listed in the same sentence
where it says "...are not supported."
Mostly I think I'm just a victim of poor wording. xs:NOTATION constructor
and xs:NOTATION casting are clearly not supported, but is xs:NOTATION itself
(even as a virtual base type) supported or not?|||Hi Mike
I think overall if you add everything up then you have to say there is no
support at all. There is no constructor so you can't create anything of that
type, you can not cast to or from so even if you had something of that type
you can't do much with it.
Have you tried using it?
John
"Mike C#" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A04FBA1C-8FB5-4461-83D3-201B858C672F@.microsoft.com...
> > Hi Mike
> >
> > On the page you posted
> > http://msdn2.microsoft.com/en-us/library/ms191231.aspx :
> >
> > "xs:NOTATION and the fully ordered subtypes of duration,
> > xdt:yearMonthDuration and xdt:dayTimeDuration, are not supported. As a
> > result, casting to or from these types is not supported."
> Thanks, that seems to indicate xs:NOTATION is not supported at all. The
> fact that xs:NOTATION is included in the chart at the top of that page seems
> to contradict that wording though...
> > On http://msdn2.microsoft.com/pt-br/library/ms189547.aspx
> >
> > "Constructors are supported for base and derived atomic XSD types.
> > However,
> > the subtypes of xs:duration, which includes xdt:yearMonthDuration and
> > xdt:dayTimeDuration, and xs:QName, xs:NMTOKEN, and xs:NOTATION are not
> > supported. User-defined atomic types that are available in the associated
> > schema collections are also available, provided they are directly or
> > indirectly derived from the following types."
> Here's where the confusion comes in - xs:NOTATION is either supported (as a
> virtual base type, as indicated on the chart on the first page and alluded
> to in the quote from the second page), or it's not supported at all (as
> indicated by the wording on the first page).
> I assume the statement from the second page doesn't really indicate that
> xs:NOTATION itself is not supported, but rather that an xs:NOTATION
> constructor is not supported. That's based on the fact that xs:QName *is*
> supported (though no constructor), and it's listed in the same sentence
> where it says "...are not supported."
> Mostly I think I'm just a victim of poor wording. xs:NOTATION constructor
> and xs:NOTATION casting are clearly not supported, but is xs:NOTATION itself
> (even as a virtual base type) supported or not?
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AC29C43A-7507-48AF-A419-3015A4654597@.microsoft.com...
> Hi Mike
> I think overall if you add everything up then you have to say there is no
> support at all. There is no constructor so you can't create anything of
> that
> type, you can not cast to or from so even if you had something of that
> type
> you can't do much with it.
> Have you tried using it?
Consider xs:QName: ( no constructor for xs:QName + no casting to or from an
xs:QName from any other type ) != no support at all. xs:QName is definitely
supported.
I haven't tried using xs:NOTATION directly, since the chart indicates it's a
"virtual base type". Presumably other types can be created from it. I
figured the good folks from Microsoft who visit these newsgroups might be
able to shed some light on how it might be used at all if it is supported as
a "virtual base type", or explain the apparent discrepancy in the
documentation if it's not supported at all.
Thanks.|||"Mike C#" <xyz@.xyz.com> wrote in message
news:uzrl9$HHHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have you tried using it?
> Consider xs:QName: ( no constructor for xs:QName + no casting to or from
> an xs:QName from any other type ) != no support at all. xs:QName is
> definitely supported.
> I haven't tried using xs:NOTATION directly, since the chart indicates it's
> a "virtual base type". Presumably other types can be created from it. I
> figured the good folks from Microsoft who visit these newsgroups might be
> able to shed some light on how it might be used at all if it is supported
> as a "virtual base type", or explain the apparent discrepancy in the
> documentation if it's not supported at all.
>
I just ran a simple test for xs:NOTATION, and the result was SQL giving me
the error message "NOTATION not supported". So it looks like it doesn't
support xs:NOTATION at all. Someone at MS should fix that chart on the
XQuery datatypes casting page.
Thanks for the help!|||Hi Mike
The chart shows casting between QNs so it would indicate some support! It
may be interesting to see what happens if you try to construct one!!
You could try sending feedback on this using the contact us option at the
bottom of the page.
John
"Mike C#" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:AC29C43A-7507-48AF-A419-3015A4654597@.microsoft.com...
> > Hi Mike
> >
> > I think overall if you add everything up then you have to say there is no
> > support at all. There is no constructor so you can't create anything of
> > that
> > type, you can not cast to or from so even if you had something of that
> > type
> > you can't do much with it.
> >
> > Have you tried using it?
> Consider xs:QName: ( no constructor for xs:QName + no casting to or from an
> xs:QName from any other type ) != no support at all. xs:QName is definitely
> supported.
> I haven't tried using xs:NOTATION directly, since the chart indicates it's a
> "virtual base type". Presumably other types can be created from it. I
> figured the good folks from Microsoft who visit these newsgroups might be
> able to shed some light on how it might be used at all if it is supported as
> a "virtual base type", or explain the apparent discrepancy in the
> documentation if it's not supported at all.
> Thanks.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi Mike
> The chart shows casting between QNs so it would indicate some support! It
> may be interesting to see what happens if you try to construct one!!
> You could try sending feedback on this using the contact us option at the
> bottom of the page.
>
Hi John
QN's don't have a constructor function per se, but there is an F&O function
to create a QN. The QN casting is strictly QN to QN. The xs:NOTATION
doesn't even work with the "instance of" operator (which was my test), and
it comes back with the error message stating explicitly that no support for
xs:NOTATION exists, so that's what I'm going with :)
Thanks|||The first thing to test is, if the type is supported in XML Schema
collections... and it is not.
CREATE XML SCHEMA COLLECTION SCX AS
N'<xsd:schema targetNamespace="urn:example/customer"
xmlns:e="urn:example/customer"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xsd:element name="buys" type="xsd:NOTATION"/>
</xsd:schema>'
returns
Msg 9337, Level 16, State 1, Line 1
The XML Schema type 'NOTATION' is not supported.
Since schema does not support the type, XQuery does not either.
If this type is important for your scenario, please file a bug report under
http://connect.microsoft.com/sqlserver
Thanks
Michael
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
>> Hi Mike
>> The chart shows casting between QNs so it would indicate some support! It
>> may be interesting to see what happens if you try to construct one!!
>> You could try sending feedback on this using the contact us option at the
>> bottom of the page.
> Hi John
> QN's don't have a constructor function per se, but there is an F&O
> function to create a QN. The QN casting is strictly QN to QN. The
> xs:NOTATION doesn't even work with the "instance of" operator (which was
> my test), and it comes back with the error message stating explicitly that
> no support for xs:NOTATION exists, so that's what I'm going with :)
> Thanks
>|||I can work around that limitation for now. It would be nice if they could
refresh the documentation on it to get rid of xs:NOTATION from the chart and
state a little more clearly that it's not supported. Right now the
statement that it's not supported is combined with a couple of other items
that are supported in various fashions, which leads to having to do tests
like this to determine if it actually is supported or not.
Thanks.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eFSlFP8HHHA.1248@.TK2MSFTNGP03.phx.gbl...
> The first thing to test is, if the type is supported in XML Schema
> collections... and it is not.
> CREATE XML SCHEMA COLLECTION SCX AS
> N'<xsd:schema targetNamespace="urn:example/customer"
> xmlns:e="urn:example/customer"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified">
> <xsd:element name="buys" type="xsd:NOTATION"/>
> </xsd:schema>'
> returns
> Msg 9337, Level 16, State 1, Line 1
> The XML Schema type 'NOTATION' is not supported.
> Since schema does not support the type, XQuery does not either.
> If this type is important for your scenario, please file a bug report
> under http://connect.microsoft.com/sqlserver
> Thanks
> Michael
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
>> Hi Mike
>> The chart shows casting between QNs so it would indicate some support!
>> It
>> may be interesting to see what happens if you try to construct one!!
>> You could try sending feedback on this using the contact us option at
>> the
>> bottom of the page.
>>
>> Hi John
>> QN's don't have a constructor function per se, but there is an F&O
>> function to create a QN. The QN casting is strictly QN to QN. The
>> xs:NOTATION doesn't even work with the "instance of" operator (which was
>> my test), and it comes back with the error message stating explicitly
>> that no support for xs:NOTATION exists, so that's what I'm going with :)
>> Thanks
>|||Hi Mike
If you find the limitation for <xsd:limitation>,pls let me know at
rohiniraj@.rediffmail.com
Im also struggling in that only.
Also if you find the limitation for
XML schema components can have an ID attribute, but SQL Server does not
store these value
pls tell me.
Thanks in advance.|||These limitations should all be documented in Books Online.
I know that we document that we do not save the ID attribute...
If any of the existing limitations are a problem, please file a bug
report/feature request at
http://connect.microsoft.com/sqlserver.
Thanks
Mike
<rohiniraj@.rediffmail.com> wrote in message
news:1166538986.101987.264990@.73g2000cwn.googlegroups.com...
> Hi Mike
> If you find the limitation for <xsd:limitation>,pls let me know at
> rohiniraj@.rediffmail.com
> Im also struggling in that only.
> Also if you find the limitation for
> XML schema components can have an ID attribute, but SQL Server does not
> store these value
> pls tell me.
> Thanks in advance.
>

[SQL 2005] XQuery xs:NOTATION

Thanks|||The first thing to test is, if the type is supported in XML Schema
collections... and it is not.
CREATE XML SCHEMA COLLECTION SCX AS
N'<xsd:schema targetNamespace="urn:example/customer"
xmlns:e="urn:example/customer"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xsd:element name="buys" type="xsd:NOTATION"/>
</xsd:schema>'
returns
Msg 9337, Level 16, State 1, Line 1
The XML Schema type 'NOTATION' is not supported.
Since schema does not support the type, XQuery does not either.
If this type is important for your scenario, please file a bug report under
http://connect.microsoft.com/sqlserver
Thanks
Michael
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi John
> QN's don't have a constructor function per se, but there is an F&O
> function to create a QN. The QN casting is strictly QN to QN. The
> xs:NOTATION doesn't even work with the "instance of" operator (which was
> my test), and it comes back with the error message stating explicitly that
> no support for xs:NOTATION exists, so that's what I'm going with

> Thanks
>|||Hi Mike
If you find the limitation for <xsd:limitation>,pls let me know at
rohiniraj@.rediffmail.com
Im also struggling in that only.
Also if you find the limitation for
XML schema components can have an ID attribute, but SQL Server does not
store these value
pls tell me.
Thanks in advance.

[SQL 2005] XQuery xs:NOTATION

I thought I read in BOL that SQL 2K5's XQuery implementation does not
support the XML Schema xs:NOTATION datatype; but I'm having a hard time
finding that page again. I was able to locate a page in BOL
(http://msdn2.microsoft.com/en-us/library/ms191231.aspx) that indicates type
casting to/from xs:NOTATION is not supported, but need to know is
xs:NOTATION actually supported at all? Any references to this information
(one way or the other) is appreciated.
Thanks!Hi Mike
On the page you posted
http://msdn2.microsoft.com/en-us/library/ms191231.aspx :
"xs:NOTATION and the fully ordered subtypes of duration,
xdt:yearMonthDuration and xdt:dayTimeDuration, are not supported. As a
result, casting to or from these types is not supported."
On http://msdn2.microsoft.com/pt-br/library/ms189547.aspx
"Constructors are supported for base and derived atomic XSD types. However,
the subtypes of xs:duration, which includes xdt:yearMonthDuration and
xdt:dayTimeDuration, and xs:QName, xs:NMTOKEN, and xs:NOTATION are not
supported. User-defined atomic types that are available in the associated
schema collections are also available, provided they are directly or
indirectly derived from the following types."
John
"Mike C#" wrote:

> I thought I read in BOL that SQL 2K5's XQuery implementation does not
> support the XML Schema xs:NOTATION datatype; but I'm having a hard time
> finding that page again. I was able to locate a page in BOL
> (http://msdn2.microsoft.com/en-us/library/ms191231.aspx) that indicates ty
pe
> casting to/from xs:NOTATION is not supported, but need to know is
> xs:NOTATION actually supported at all? Any references to this information
> (one way or the other) is appreciated.
> Thanks!
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A04FBA1C-8FB5-4461-83D3-201B858C672F@.microsoft.com...
> Hi Mike
> On the page you posted
> http://msdn2.microsoft.com/en-us/library/ms191231.aspx :
> "xs:NOTATION and the fully ordered subtypes of duration,
> xdt:yearMonthDuration and xdt:dayTimeDuration, are not supported. As a
> result, casting to or from these types is not supported."
Thanks, that seems to indicate xs:NOTATION is not supported at all. The
fact that xs:NOTATION is included in the chart at the top of that page seems
to contradict that wording though...

> On http://msdn2.microsoft.com/pt-br/library/ms189547.aspx
> "Constructors are supported for base and derived atomic XSD types.
> However,
> the subtypes of xs:duration, which includes xdt:yearMonthDuration and
> xdt:dayTimeDuration, and xs:QName, xs:NMTOKEN, and xs:NOTATION are not
> supported. User-defined atomic types that are available in the associated
> schema collections are also available, provided they are directly or
> indirectly derived from the following types."
Here's where the confusion comes in - xs:NOTATION is either supported (as a
virtual base type, as indicated on the chart on the first page and alluded
to in the quote from the second page), or it's not supported at all (as
indicated by the wording on the first page).
I assume the statement from the second page doesn't really indicate that
xs:NOTATION itself is not supported, but rather that an xs:NOTATION
constructor is not supported. That's based on the fact that xs:QName *is*
supported (though no constructor), and it's listed in the same sentence
where it says "...are not supported."
Mostly I think I'm just a victim of poor wording. xs:NOTATION constructor
and xs:NOTATION casting are clearly not supported, but is xs:NOTATION itself
(even as a virtual base type) supported or not?|||Hi Mike
I think overall if you add everything up then you have to say there is no
support at all. There is no constructor so you can't create anything of that
type, you can not cast to or from so even if you had something of that type
you can't do much with it.
Have you tried using it?
John
"Mike C#" wrote:

> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A04FBA1C-8FB5-4461-83D3-201B858C672F@.microsoft.com...
> Thanks, that seems to indicate xs:NOTATION is not supported at all. The
> fact that xs:NOTATION is included in the chart at the top of that page see
ms
> to contradict that wording though...
>
> Here's where the confusion comes in - xs:NOTATION is either supported (as
a
> virtual base type, as indicated on the chart on the first page and alluded
> to in the quote from the second page), or it's not supported at all (as
> indicated by the wording on the first page).
> I assume the statement from the second page doesn't really indicate that
> xs:NOTATION itself is not supported, but rather that an xs:NOTATION
> constructor is not supported. That's based on the fact that xs:QName *is*
> supported (though no constructor), and it's listed in the same sentence
> where it says "...are not supported."
> Mostly I think I'm just a victim of poor wording. xs:NOTATION constructor
> and xs:NOTATION casting are clearly not supported, but is xs:NOTATION itse
lf
> (even as a virtual base type) supported or not?
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AC29C43A-7507-48AF-A419-3015A4654597@.microsoft.com...
> Hi Mike
> I think overall if you add everything up then you have to say there is no
> support at all. There is no constructor so you can't create anything of
> that
> type, you can not cast to or from so even if you had something of that
> type
> you can't do much with it.
> Have you tried using it?
Consider xs:QName: ( no constructor for xs:QName + no casting to or from an
xs:QName from any other type ) != no support at all. xs:QName is definitely
supported.
I haven't tried using xs:NOTATION directly, since the chart indicates it's a
"virtual base type". Presumably other types can be created from it. I
figured the good folks from Microsoft who visit these newsgroups might be
able to shed some light on how it might be used at all if it is supported as
a "virtual base type", or explain the apparent discrepancy in the
documentation if it's not supported at all.
Thanks.|||"Mike C#" <xyz@.xyz.com> wrote in message
news:uzrl9$HHHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Consider xs:QName: ( no constructor for xs:QName + no casting to or from
> an xs:QName from any other type ) != no support at all. xs:QName is
> definitely supported.
> I haven't tried using xs:NOTATION directly, since the chart indicates it's
> a "virtual base type". Presumably other types can be created from it. I
> figured the good folks from Microsoft who visit these newsgroups might be
> able to shed some light on how it might be used at all if it is supported
> as a "virtual base type", or explain the apparent discrepancy in the
> documentation if it's not supported at all.
>
I just ran a simple test for xs:NOTATION, and the result was SQL giving me
the error message "NOTATION not supported". So it looks like it doesn't
support xs:NOTATION at all. Someone at MS should fix that chart on the
XQuery datatypes casting page.
Thanks for the help!|||Hi Mike
The chart shows casting between QNs so it would indicate some support! It
may be interesting to see what happens if you try to construct one!!
You could try sending feedback on this using the contact us option at the
bottom of the page.
John
"Mike C#" wrote:

> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:AC29C43A-7507-48AF-A419-3015A4654597@.microsoft.com...
> Consider xs:QName: ( no constructor for xs:QName + no casting to or from
an
> xs:QName from any other type ) != no support at all. xs:QName is definite
ly
> supported.
> I haven't tried using xs:NOTATION directly, since the chart indicates it's
a
> "virtual base type". Presumably other types can be created from it. I
> figured the good folks from Microsoft who visit these newsgroups might be
> able to shed some light on how it might be used at all if it is supported
as
> a "virtual base type", or explain the apparent discrepancy in the
> documentation if it's not supported at all.
> Thanks.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi Mike
> The chart shows casting between QNs so it would indicate some support! It
> may be interesting to see what happens if you try to construct one!!
> You could try sending feedback on this using the contact us option at the
> bottom of the page.
>
Hi John
QN's don't have a constructor function per se, but there is an F&O function
to create a QN. The QN casting is strictly QN to QN. The xs:NOTATION
doesn't even work with the "instance of" operator (which was my test), and
it comes back with the error message stating explicitly that no support for
xs:NOTATION exists, so that's what I'm going with :)
Thanks|||The first thing to test is, if the type is supported in XML Schema
collections... and it is not.
CREATE XML SCHEMA COLLECTION SCX AS
N'<xsd:schema targetNamespace="urn:example/customer"
xmlns:e="urn:example/customer"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xsd:element name="buys" type="xsd:NOTATION"/>
</xsd:schema>'
returns
Msg 9337, Level 16, State 1, Line 1
The XML Schema type 'NOTATION' is not supported.
Since schema does not support the type, XQuery does not either.
If this type is important for your scenario, please file a bug report under
http://connect.microsoft.com/sqlserver
Thanks
Michael
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi John
> QN's don't have a constructor function per se, but there is an F&O
> function to create a QN. The QN casting is strictly QN to QN. The
> xs:NOTATION doesn't even work with the "instance of" operator (which was
> my test), and it comes back with the error message stating explicitly that
> no support for xs:NOTATION exists, so that's what I'm going with :)
> Thanks
>|||I can work around that limitation for now. It would be nice if they could
refresh the documentation on it to get rid of xs:NOTATION from the chart and
state a little more clearly that it's not supported. Right now the
statement that it's not supported is combined with a couple of other items
that are supported in various fashions, which leads to having to do tests
like this to determine if it actually is supported or not.
Thanks.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eFSlFP8HHHA.1248@.TK2MSFTNGP03.phx.gbl...
> The first thing to test is, if the type is supported in XML Schema
> collections... and it is not.
> CREATE XML SCHEMA COLLECTION SCX AS
> N'<xsd:schema targetNamespace="urn:example/customer"
> xmlns:e="urn:example/customer"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified">
> <xsd:element name="buys" type="xsd:NOTATION"/>
> </xsd:schema>'
> returns
> Msg 9337, Level 16, State 1, Line 1
> The XML Schema type 'NOTATION' is not supported.
> Since schema does not support the type, XQuery does not either.
> If this type is important for your scenario, please file a bug report
> under http://connect.microsoft.com/sqlserver
> Thanks
> Michael
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>

[SQL 2005] XQuery xs:NOTATION

Thanks
|||The first thing to test is, if the type is supported in XML Schema
collections... and it is not.
CREATE XML SCHEMA COLLECTION SCX AS
N'<xsd:schema targetNamespace="urn:example/customer"
xmlns:e="urn:example/customer"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xsd:element name="buys" type="xsd:NOTATION"/>
</xsd:schema>'
returns
Msg 9337, Level 16, State 1, Line 1
The XML Schema type 'NOTATION' is not supported.
Since schema does not support the type, XQuery does not either.
If this type is important for your scenario, please file a bug report under
http://connect.microsoft.com/sqlserver
Thanks
Michael
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi John
> QN's don't have a constructor function per se, but there is an F&O
> function to create a QN. The QN casting is strictly QN to QN. The
> xs:NOTATION doesn't even work with the "instance of" operator (which was
> my test), and it comes back with the error message stating explicitly that
> no support for xs:NOTATION exists, so that's what I'm going with

> Thanks
>
|||I can work around that limitation for now. It would be nice if they could
refresh the documentation on it to get rid of xs:NOTATION from the chart and
state a little more clearly that it's not supported. Right now the
statement that it's not supported is combined with a couple of other items
that are supported in various fashions, which leads to having to do tests
like this to determine if it actually is supported or not.
Thanks.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eFSlFP8HHHA.1248@.TK2MSFTNGP03.phx.gbl...
> The first thing to test is, if the type is supported in XML Schema
> collections... and it is not.
> CREATE XML SCHEMA COLLECTION SCX AS
> N'<xsd:schema targetNamespace="urn:example/customer"
> xmlns:e="urn:example/customer"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified">
> <xsd:element name="buys" type="xsd:NOTATION"/>
> </xsd:schema>'
> returns
> Msg 9337, Level 16, State 1, Line 1
> The XML Schema type 'NOTATION' is not supported.
> Since schema does not support the type, XQuery does not either.
> If this type is important for your scenario, please file a bug report
> under http://connect.microsoft.com/sqlserver
> Thanks
> Michael
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>

[SQL 2005] XQuery xs:NOTATION

Thanks
|||The first thing to test is, if the type is supported in XML Schema
collections... and it is not.
CREATE XML SCHEMA COLLECTION SCX AS
N'<xsd:schema targetNamespace="urn:example/customer"
xmlns:e="urn:example/customer"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xsd:element name="buys" type="xsd:NOTATION"/>
</xsd:schema>'
returns
Msg 9337, Level 16, State 1, Line 1
The XML Schema type 'NOTATION' is not supported.
Since schema does not support the type, XQuery does not either.
If this type is important for your scenario, please file a bug report under
http://connect.microsoft.com/sqlserver
Thanks
Michael
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23RoXrrTHHHA.1816@.TK2MSFTNGP06.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7C916F7A-ED18-46E3-86F4-4E5B2E93D1A5@.microsoft.com...
> Hi John
> QN's don't have a constructor function per se, but there is an F&O
> function to create a QN. The QN casting is strictly QN to QN. The
> xs:NOTATION doesn't even work with the "instance of" operator (which was
> my test), and it comes back with the error message stating explicitly that
> no support for xs:NOTATION exists, so that's what I'm going with

Friday, February 24, 2012

[help] SSIS File Configuration (XML)

We are deploying our SSIS packages into different folders. For example:
\Test1
\test2
\production

Test1 points to the Test1 database, etc.

So, I configured my SSIS package to use a database connection called dbMAIN.

I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C:\test1\, the same place as my ssis.dtsx file.

I want to copy my dtsx file and my dtsconfig file into \test2 and \production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!

How do I dynamically change it?

I tried to hardcode the location to ".\Global.dtsconfig", but that did not work! Please advise!I found the solution. When you call an executable, you can use the /ConfigFile option. Example:

dtexec.exe /file "d:\myFile.dtsx" /configfile "d:\test1\Test1.dtsconfig"

When myfile.dtsx is run, it will use the test1.dtsconfig file to get all of its configurations.

Hooah!

~Le

Monday, February 13, 2012

@XMLVariable.query

How do I get the value of ID when an attribute is part of the XML? The
following script shows the problem. It should be ready to execute,
depending on wrapping. Test1 works; test2 and test3 have an attribute, and
do not work.
Thanks,
Richard
set nocount on
use tempdb
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet>
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet
xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
goDoes this help?
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'declare default element namespace
"http://tempuri.org/DataSet.xsd";
data( /DataSet/Request/ID )' ) as Test2 ;
with xmlnamespaces(default 'http://tempuri.org/DataSet.xsd' )
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2|||Your xml document is using a default namespace which must be defined somewhe
re.
Here is an example of one way to do it, note the use of the semicolon.
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>';
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/DataSet.xsd')
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
Dan

> How do I get the value of ID when an attribute is part of the XML?
> The following script shows the problem. It should be ready to
> execute, depending on wrapping. Test1 works; test2 and test3 have an
> attribute, and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3 go
>|||Perfect! Thanks for the quick replies.
"Richard" <napa299@.yahoo.com> wrote in message
news:%23c%23EM$miGHA.4284@.TK2MSFTNGP05.phx.gbl...
> How do I get the value of ID when an attribute is part of the XML? The
> following script shows the problem. It should be ready to execute,
> depending on wrapping. Test1 works; test2 and test3 have an attribute,
> and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
> go
>

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything come
s
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W

1Month1Indicator
)[1]' , 'char(1)' ) as P6_W

1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanksKarl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instanc
e
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything co
mes
> as NULL), however if i manually take out toe xmlns part of the xml, it wor
ks
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) a
s
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) A
S
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' )
as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W

1Month1Indicat
or)[1]' , 'char(1)' ) as P6_W

1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]
'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes fro
m
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime'
)
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) a
s
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal
?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the detail
s
> thanks again

Tuesday, March 20, 2012

[XML Task] Error: An error occurred with the following error message: There are multiple root el

[XML Task] Error: An error occurred with the following error message: There are multiple roo

Sunday, March 11, 2012

[SQL 2005] XQuery xs:NOTATION

[SQL 2005] XQuery xs:NOTATION

[SQL 2005] XQuery xs:NOTATION

[SQL 2005] XQuery xs:NOTATION

[SQL 2005] XQuery xs:NOTATION

Friday, February 24, 2012

[help] SSIS File Configuration (XML)

Monday, February 13, 2012

@XMLVariable.query

@xml.value against XML data with namespaces

sql rowcount

Blog Archive

About Me