@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 comes
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/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
@.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
Karl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instance
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 comes
> 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/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
> @.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 from
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' ) 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