Monday, February 20, 2012

OPENXML SELECT does not work with one of the definitions

Problem with XML
Apparently there seems to be 2 different XML definitions of a string;
The first one is like this:
'<people><person name="John Doe" age="30"/></people>'
The second version is like this:
'<?xml version ="1.0"?><BarcodeTTP><BerichtIndicator>0</BerichtIndicator><Channel>GJN
</Channel><PartyCode>gjn</PartyCode><PUIC>gjn0000114</PUIC><Resultaat>0</Resultaat>
<ResultaatTekst>houdoe</ResultaatTekst></BarcodeTTP>'
After preparing the output and so I wish to SELECT the DATA in one ROW.
The first structure I can get output with.
SELECT [name], [age]
FROM OPENXML(@.id, '/people/person')
WITH
( name varchar(20) ,
age varchar(20) )
The Second Structure
SELECT BerichtIndicator, Channel, PartyCode, PUIC, Resultaat,
ResultaatTekst
FROM OPENXML(@.id, '/BarcodeTTP')
WITH
(BerichtIndicator int '@.BerichtIndicator',
Channel varchar(10) '@.Channel',
PartyCode varchar(20) '@.PartyCode',
PUIC varchar(20) '@.PUIC',
Resultaat int '@.Resultaat',
ResultaatTekst varchar(100) '@.ResultaatTekst')
Gives me one row with alll columns filled with NULL values.
What do I wrong ? Or do I have to do something with the structure ?
Arno de Jong, The Netherlands.
declare @.id int
exec sp_xml_preparedocument @.id OUTPUT, N'<?xml version="1.0"?>
<BarcodeTTP>
<BerichtIndicator>0</BerichtIndicator>
<Channel>GJN</Channel>
<PartyCode>gjn</PartyCode>
<PUIC>gjn0000114</PUIC>
<Resultaat>0</Resultaat>
<ResultaatTekst>houdoe</ResultaatTekst>
</BarcodeTTP>
'
SELECT BerichtIndicator, Channel, PartyCode, PUIC, Resultaat, ResultaatTekst
FROM OPENXML(@.id, '/BarcodeTTP', 2)
WITH (BerichtIndicator int,
Channel varchar(10),
PartyCode varchar(20),
PUIC varchar(20),
Resultaat int,
ResultaatTekst varchar(100))
exec sp_xml_removedocument @.id
Martin
"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:ct0fjc$nfo$1@.reader13.wxs.nl...
> Problem with XML
>
> Apparently there seems to be 2 different XML definitions of a string;
>
> The first one is like this:
> '<people><person name="John Doe" age="30"/></people>'
>
> The second version is like this:
> '<?xml version
="1.0"?><BarcodeTTP><BerichtIndicator>0</BerichtIndicator><Channel>GJN
>
</Channel><PartyCode>gjn</PartyCode><PUIC>gjn0000114</PUIC><Resultaat>0</Res
ultaat>
> <ResultaatTekst>houdoe</ResultaatTekst></BarcodeTTP>'
>
>
> After preparing the output and so I wish to SELECT the DATA in one ROW.
>
> The first structure I can get output with.
>
> SELECT [name], [age]
> FROM OPENXML(@.id, '/people/person')
> WITH
> ( name varchar(20) ,
> age varchar(20) )
>
> The Second Structure
>
> SELECT BerichtIndicator, Channel, PartyCode, PUIC, Resultaat,
> ResultaatTekst
> FROM OPENXML(@.id, '/BarcodeTTP')
> WITH
> (BerichtIndicator int '@.BerichtIndicator',
> Channel varchar(10) '@.Channel',
> PartyCode varchar(20) '@.PartyCode',
> PUIC varchar(20) '@.PUIC',
> Resultaat int '@.Resultaat',
> ResultaatTekst varchar(100) '@.ResultaatTekst')
>
> Gives me one row with alll columns filled with NULL values.
> What do I wrong ? Or do I have to do something with the structure ?
> Arno de Jong, The Netherlands.
>
>

No comments:

Post a Comment