OPENXML Question

Hello,
This is my XML data:
<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>
How would I use OPENXML to get the data in this format:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
-- -- -- -- --
a B C d
Thanks.Hi
Try something like:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
N'<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>'
-- Use OPENXML to provide rowset consisting of customer data.
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
) A
EXEC sp_xml_removedocument @.hdoc
The subquery and max functions are because you want to pivot the output. If
you can work with
SELECT [KEY], [value]
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
transformation.
John
"Ganesh Muthuvelu" wrote:
> Hello,
> This is my XML data:
> <RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>
> How would I use OPENXML to get the data in this format:
> COLUMN1 COLUMN2 COLUMN3 COLUMN4
> -- -- -- -- --
> a B C d
> Thanks.|||John,
Thanks for the reply. Could you tell me what this means in your query:?
WITH ( [KEY] char(7), value char(1) '.' )
I understand that the [KEY] and value are columns of char(7) and char(1) ,
but what does the '.' mean?.
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
Thanks.
"John Bell" wrote:
> Hi
> Try something like:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> N'<RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>'
> -- Use OPENXML to provide rowset consisting of customer data.
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> ) A
> EXEC sp_xml_removedocument @.hdoc
> The subquery and max functions are because you want to pivot the output. If
> you can work with
> SELECT [KEY], [value]
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> transformation.
> John
> "Ganesh Muthuvelu" wrote:
> > Hello,
> > This is my XML data:
> > <RESPONSE>
> > <FIELDS>
> > <FIELD KEY="COLUMN1">a</FIELD>
> > <FIELD KEY="COLUMN2">B</FIELD>
> > <FIELD KEY="COLUMN3">C</FIELD>
> > <FIELD KEY="COLUMN4">d</FIELD>
> > </FIELDS>
> > </RESPONSE>
> >
> > How would I use OPENXML to get the data in this format:
> > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > -- -- -- -- --
> > a B C d
> >
> > Thanks.|||Hi
'.' is an abbreviation for self::node() Check out "Specifying a Node Test in
the Location Path" in Books online.
I could have written:
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) 'self::node()' )
) A
John
"Ganesh Muthuvelu" wrote:
> John,
> Thanks for the reply. Could you tell me what this means in your query:?
> WITH ( [KEY] char(7), value char(1) '.' )
> I understand that the [KEY] and value are columns of char(7) and char(1) ,
> but what does the '.' mean?.
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> Thanks.
>
>
> "John Bell" wrote:
> > Hi
> >
> > Try something like:
> >
> > DECLARE @.hDoc int
> > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > N'<RESPONSE>
> > <FIELDS>
> > <FIELD KEY="COLUMN1">a</FIELD>
> > <FIELD KEY="COLUMN2">B</FIELD>
> > <FIELD KEY="COLUMN3">C</FIELD>
> > <FIELD KEY="COLUMN4">d</FIELD>
> > </FIELDS>
> > </RESPONSE>'
> > -- Use OPENXML to provide rowset consisting of customer data.
> > SELECT MAX(COLUMN1) AS COLUMN1,
> > MAX(COLUMN2) AS COLUMN2,
> > MAX(COLUMN3) AS COLUMN3,
> > MAX(COLUMN4) AS COLUMN4
> > FROM (
> > SELECT [KEY],
> > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) '.' )
> > ) A
> >
> > EXEC sp_xml_removedocument @.hdoc
> >
> > The subquery and max functions are because you want to pivot the output. If
> > you can work with
> >
> > SELECT [KEY], [value]
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) '.' )
> >
> > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > transformation.
> >
> > John
> >
> > "Ganesh Muthuvelu" wrote:
> >
> > > Hello,
> > > This is my XML data:
> > > <RESPONSE>
> > > <FIELDS>
> > > <FIELD KEY="COLUMN1">a</FIELD>
> > > <FIELD KEY="COLUMN2">B</FIELD>
> > > <FIELD KEY="COLUMN3">C</FIELD>
> > > <FIELD KEY="COLUMN4">d</FIELD>
> > > </FIELDS>
> > > </RESPONSE>
> > >
> > > How would I use OPENXML to get the data in this format:
> > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > -- -- -- -- --
> > > a B C d
> > >
> > > Thanks.|||John,
Thanks again, Could you also see the second part of the question.
*************
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some
sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
************
Thanks.
"John Bell" wrote:
> Hi
> '.' is an abbreviation for self::node() Check out "Specifying a Node Test in
> the Location Path" in Books online.
> I could have written:
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) 'self::node()' )
> ) A
>
> John
> "Ganesh Muthuvelu" wrote:
> > John,
> > Thanks for the reply. Could you tell me what this means in your query:?
> >
> > WITH ( [KEY] char(7), value char(1) '.' )
> >
> > I understand that the [KEY] and value are columns of char(7) and char(1) ,
> > but what does the '.' mean?.
> >
> > Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> > would I identify each row uniquely?. The input XML has to come with some sort
> > of primary keys then?. As you may see, the MAX would work if there is only
> > one row - if there are mulitple rows and the input XML does not have any
> > "primary keys", how would I make this work?.
> >
> > Thanks.
> >
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Try something like:
> > >
> > > DECLARE @.hDoc int
> > > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > > N'<RESPONSE>
> > > <FIELDS>
> > > <FIELD KEY="COLUMN1">a</FIELD>
> > > <FIELD KEY="COLUMN2">B</FIELD>
> > > <FIELD KEY="COLUMN3">C</FIELD>
> > > <FIELD KEY="COLUMN4">d</FIELD>
> > > </FIELDS>
> > > </RESPONSE>'
> > > -- Use OPENXML to provide rowset consisting of customer data.
> > > SELECT MAX(COLUMN1) AS COLUMN1,
> > > MAX(COLUMN2) AS COLUMN2,
> > > MAX(COLUMN3) AS COLUMN3,
> > > MAX(COLUMN4) AS COLUMN4
> > > FROM (
> > > SELECT [KEY],
> > > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > char(1) '.' )
> > > ) A
> > >
> > > EXEC sp_xml_removedocument @.hdoc
> > >
> > > The subquery and max functions are because you want to pivot the output. If
> > > you can work with
> > >
> > > SELECT [KEY], [value]
> > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > char(1) '.' )
> > >
> > > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > > transformation.
> > >
> > > John
> > >
> > > "Ganesh Muthuvelu" wrote:
> > >
> > > > Hello,
> > > > This is my XML data:
> > > > <RESPONSE>
> > > > <FIELDS>
> > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > </FIELDS>
> > > > </RESPONSE>
> > > >
> > > > How would I use OPENXML to get the data in this format:
> > > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > > -- -- -- -- --
> > > > a B C d
> > > >
> > > > Thanks.|||Hi
Your XML does not lend itself to pivoting if you have multiple response
segments. You would probably need to load it into a temporary table where you
can create an artificial key and then generate the pivot from that.
Alternatively you may be able to use a transform and the position function to
achieve something similar. If you pivoted on the client you would not need
this extra step.
If you had multiple response segments, you would need a new root node.
HTH
John
"Ganesh Muthuvelu" wrote:
> John,
> Thanks again, Could you also see the second part of the question.
> *************
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some
> sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> ************
> Thanks.
> "John Bell" wrote:
> > Hi
> >
> > '.' is an abbreviation for self::node() Check out "Specifying a Node Test in
> > the Location Path" in Books online.
> >
> > I could have written:
> >
> > SELECT MAX(COLUMN1) AS COLUMN1,
> > MAX(COLUMN2) AS COLUMN2,
> > MAX(COLUMN3) AS COLUMN3,
> > MAX(COLUMN4) AS COLUMN4
> > FROM (
> > SELECT [KEY],
> > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) 'self::node()' )
> > ) A
> >
> >
> > John
> >
> > "Ganesh Muthuvelu" wrote:
> >
> > > John,
> > > Thanks for the reply. Could you tell me what this means in your query:?
> > >
> > > WITH ( [KEY] char(7), value char(1) '.' )
> > >
> > > I understand that the [KEY] and value are columns of char(7) and char(1) ,
> > > but what does the '.' mean?.
> > >
> > > Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> > > would I identify each row uniquely?. The input XML has to come with some sort
> > > of primary keys then?. As you may see, the MAX would work if there is only
> > > one row - if there are mulitple rows and the input XML does not have any
> > > "primary keys", how would I make this work?.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Try something like:
> > > >
> > > > DECLARE @.hDoc int
> > > > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > > > N'<RESPONSE>
> > > > <FIELDS>
> > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > </FIELDS>
> > > > </RESPONSE>'
> > > > -- Use OPENXML to provide rowset consisting of customer data.
> > > > SELECT MAX(COLUMN1) AS COLUMN1,
> > > > MAX(COLUMN2) AS COLUMN2,
> > > > MAX(COLUMN3) AS COLUMN3,
> > > > MAX(COLUMN4) AS COLUMN4
> > > > FROM (
> > > > SELECT [KEY],
> > > > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > > > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > > > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > > > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > > char(1) '.' )
> > > > ) A
> > > >
> > > > EXEC sp_xml_removedocument @.hdoc
> > > >
> > > > The subquery and max functions are because you want to pivot the output. If
> > > > you can work with
> > > >
> > > > SELECT [KEY], [value]
> > > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > > char(1) '.' )
> > > >
> > > > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > > > transformation.
> > > >
> > > > John
> > > >
> > > > "Ganesh Muthuvelu" wrote:
> > > >
> > > > > Hello,
> > > > > This is my XML data:
> > > > > <RESPONSE>
> > > > > <FIELDS>
> > > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > > </FIELDS>
> > > > > </RESPONSE>
> > > > >
> > > > > How would I use OPENXML to get the data in this format:
> > > > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > > > -- -- -- -- --
> > > > > a B C d
> > > > >
> > > > > Thanks.