In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.
Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com@.207.46.24 8.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
sql
Showing posts with label retrieving. Show all posts
Showing posts with label retrieving. Show all posts
Monday, March 26, 2012
retrieving XML issues - SQL 2000
In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com
@.207.46.248.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com
@.207.46.248.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
Retrieving XML from SQL SERVER
Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w
I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w
I was ill.The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael
Retrieving XML from SQL SERVER
Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options ?
Can I use a huge template file for representing the whole hierarchy ?
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.com
In SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options ?
> Can I use a huge template file for representing the whole hierarchy ?
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>
|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
>
|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish
|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegr oups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>
|||Hello Michael,
Sorry it took so long, last week I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
> Does that help?
> Best regards
> Michael
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options ?
Can I use a huge template file for representing the whole hierarchy ?
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.com
In SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options ?
> Can I use a huge template file for representing the whole hierarchy ?
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>
|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
>
|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish
|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegr oups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>
|||Hello Michael,
Sorry it took so long, last week I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
> Does that help?
> Best regards
> Michael
Retrieving XML from a TEXT field.
(SQL Server 2000, SP3)
Hello all!
I *know* that this question has probably been asked and answered a number of times, but I
don't think I crafted the appropriate search terms for Google and sqlxml.org. Please bear
with me.
If I have a relatively simple, but large, XML document stored as a TEXT column in a table,
is there any easy way to take all table rows (of which there aren't many) and somehow call
OPENXML with that TEXT column to process the XML documents therein, and essentially
produce a "native" SQL Server result set based on that XML?
Thanks for any help you can provide!
John PetersonThanks for the help, Michael!
Out of curiosity, do you have a pointer to a sample of your Proposal #1? That sounds like
an interesting approach (though, perhaps "expensive" -- but this would be for a function
that's not heavily used in our system).
Thanks for any additional help you can provide!
John Peterson
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2000 has the limitation that you cannot define variables of type
> TEXT/NTEXT. There are two expensive workarounds, one of which may not
> continue to work in Yukon (although in Yukon you do not need that workaround
> anymore):
> 1. Pass the data out of the server and back in as a stored proc parameter of
> type NTEXT/TEXT using the sp_OA stored procs.
> 2. Use nested EXECUTEs and a cursor to copy the XML into a string literal
> argument to sp_xml_preparedocument.
> The second one is the one that may not work anymore.
> Best regards
> Michael
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I *know* that this question has probably been asked and answered a number
> of times, but I
> > don't think I crafted the appropriate search terms for Google and
> sqlxml.org. Please bear
> > with me.
> >
> > If I have a relatively simple, but large, XML document stored as a TEXT
> column in a table,
> > is there any easy way to take all table rows (of which there aren't many)
> and somehow call
> > OPENXML with that TEXT column to process the XML documents therein, and
> essentially
> > produce a "native" SQL Server result set based on that XML?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||I dont know any easy way...
The way I solve it was to call a DTS that pases the text data to a stored
procedure as a parameter.
"John Peterson" <j0hnp@.comcast.net> escribió en el mensaje
news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I *know* that this question has probably been asked and answered a number
of times, but I
> don't think I crafted the appropriate search terms for Google and
sqlxml.org. Please bear
> with me.
> If I have a relatively simple, but large, XML document stored as a TEXT
column in a table,
> is there any easy way to take all table rows (of which there aren't many)
and somehow call
> OPENXML with that TEXT column to process the XML documents therein, and
essentially
> produce a "native" SQL Server result set based on that XML?
> Thanks for any help you can provide!
> John Peterson
>|||Since I had a hard-drive fail on me that contained my sample data, I
currently do not have a sample. Sorry.
The HD is in recovery and if I get the data back (and I remember this thread
after my summer vacation), I will check...
Maybe somebody else has some examples (see also other related threads such
as "Text in Stored Procedure").
Best regards
Michael
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23V9l7CWVDHA.2040@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help, Michael!
> Out of curiosity, do you have a pointer to a sample of your Proposal #1?
That sounds like
> an interesting approach (though, perhaps "expensive" -- but this would be
for a function
> that's not heavily used in our system).
> Thanks for any additional help you can provide!
> John Peterson
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > SQL Server 2000 has the limitation that you cannot define variables of
type
> > TEXT/NTEXT. There are two expensive workarounds, one of which may not
> > continue to work in Yukon (although in Yukon you do not need that
workaround
> > anymore):
> >
> > 1. Pass the data out of the server and back in as a stored proc
parameter of
> > type NTEXT/TEXT using the sp_OA stored procs.
> > 2. Use nested EXECUTEs and a cursor to copy the XML into a string
literal
> > argument to sp_xml_preparedocument.
> >
> > The second one is the one that may not work anymore.
> >
> > Best regards
> > Michael
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I *know* that this question has probably been asked and answered a
number
> > of times, but I
> > > don't think I crafted the appropriate search terms for Google and
> > sqlxml.org. Please bear
> > > with me.
> > >
> > > If I have a relatively simple, but large, XML document stored as a
TEXT
> > column in a table,
> > > is there any easy way to take all table rows (of which there aren't
many)
> > and somehow call
> > > OPENXML with that TEXT column to process the XML documents therein,
and
> > essentially
> > > produce a "native" SQL Server result set based on that XML?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Hello all!
I *know* that this question has probably been asked and answered a number of times, but I
don't think I crafted the appropriate search terms for Google and sqlxml.org. Please bear
with me.
If I have a relatively simple, but large, XML document stored as a TEXT column in a table,
is there any easy way to take all table rows (of which there aren't many) and somehow call
OPENXML with that TEXT column to process the XML documents therein, and essentially
produce a "native" SQL Server result set based on that XML?
Thanks for any help you can provide!
John PetersonThanks for the help, Michael!
Out of curiosity, do you have a pointer to a sample of your Proposal #1? That sounds like
an interesting approach (though, perhaps "expensive" -- but this would be for a function
that's not heavily used in our system).
Thanks for any additional help you can provide!
John Peterson
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2000 has the limitation that you cannot define variables of type
> TEXT/NTEXT. There are two expensive workarounds, one of which may not
> continue to work in Yukon (although in Yukon you do not need that workaround
> anymore):
> 1. Pass the data out of the server and back in as a stored proc parameter of
> type NTEXT/TEXT using the sp_OA stored procs.
> 2. Use nested EXECUTEs and a cursor to copy the XML into a string literal
> argument to sp_xml_preparedocument.
> The second one is the one that may not work anymore.
> Best regards
> Michael
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I *know* that this question has probably been asked and answered a number
> of times, but I
> > don't think I crafted the appropriate search terms for Google and
> sqlxml.org. Please bear
> > with me.
> >
> > If I have a relatively simple, but large, XML document stored as a TEXT
> column in a table,
> > is there any easy way to take all table rows (of which there aren't many)
> and somehow call
> > OPENXML with that TEXT column to process the XML documents therein, and
> essentially
> > produce a "native" SQL Server result set based on that XML?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||I dont know any easy way...
The way I solve it was to call a DTS that pases the text data to a stored
procedure as a parameter.
"John Peterson" <j0hnp@.comcast.net> escribió en el mensaje
news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I *know* that this question has probably been asked and answered a number
of times, but I
> don't think I crafted the appropriate search terms for Google and
sqlxml.org. Please bear
> with me.
> If I have a relatively simple, but large, XML document stored as a TEXT
column in a table,
> is there any easy way to take all table rows (of which there aren't many)
and somehow call
> OPENXML with that TEXT column to process the XML documents therein, and
essentially
> produce a "native" SQL Server result set based on that XML?
> Thanks for any help you can provide!
> John Peterson
>|||Since I had a hard-drive fail on me that contained my sample data, I
currently do not have a sample. Sorry.
The HD is in recovery and if I get the data back (and I remember this thread
after my summer vacation), I will check...
Maybe somebody else has some examples (see also other related threads such
as "Text in Stored Procedure").
Best regards
Michael
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23V9l7CWVDHA.2040@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help, Michael!
> Out of curiosity, do you have a pointer to a sample of your Proposal #1?
That sounds like
> an interesting approach (though, perhaps "expensive" -- but this would be
for a function
> that's not heavily used in our system).
> Thanks for any additional help you can provide!
> John Peterson
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > SQL Server 2000 has the limitation that you cannot define variables of
type
> > TEXT/NTEXT. There are two expensive workarounds, one of which may not
> > continue to work in Yukon (although in Yukon you do not need that
workaround
> > anymore):
> >
> > 1. Pass the data out of the server and back in as a stored proc
parameter of
> > type NTEXT/TEXT using the sp_OA stored procs.
> > 2. Use nested EXECUTEs and a cursor to copy the XML into a string
literal
> > argument to sp_xml_preparedocument.
> >
> > The second one is the one that may not work anymore.
> >
> > Best regards
> > Michael
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I *know* that this question has probably been asked and answered a
number
> > of times, but I
> > > don't think I crafted the appropriate search terms for Google and
> > sqlxml.org. Please bear
> > > with me.
> > >
> > > If I have a relatively simple, but large, XML document stored as a
TEXT
> > column in a table,
> > > is there any easy way to take all table rows (of which there aren't
many)
> > and somehow call
> > > OPENXML with that TEXT column to process the XML documents therein,
and
> > essentially
> > > produce a "native" SQL Server result set based on that XML?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Friday, March 23, 2012
Retrieving XML Datatype to a Record Set
I'm sure this is a simple thing for you guru's out there. Working in SQL 2000
I have a datatype in a colunm that is I assume is XML. I need a way to parse this so that the output is in a normal table output. I then want to join the results with other record from the same table. The nodes are all the same.
Ultimately, the data will be analyzed in reporting services.
Here is a sample of the xml data saved in a field. Thanks for your time.
<interactions><interaction index="0" id="I0001-1" timestamp="2007-07-10T14:14:00" weighting="1" type="true-false" latency="PT3S" learner_response="true" result="correct" description="Tire center employees should know where to access."><objectives><objective index="0" id="I0001-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="1" id="I0002-1" timestamp="2007-07-10T14:14:02" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="incorrect" description=" does not sell used tires or any tire that has previously been mounted on a rim and driven on."><objectives><objective index="0" id="I0002-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="2" id="I0003-1" timestamp="2007-07-10T14:14:05" weighting="1" type="choice" latency="PT3S" learner_response="Taken" result="incorrect" description="Tire center supplies should not be:"><objectives><objective index="0" id="I0003-1"/></objectives><corr_resps><corr_resp index="0" pattern="All_apply"/></corr_resps></interaction><interaction index="3" id="I0004-1" timestamp="2007-07-10T14:14:08" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="correct" description="Employees have the option to wear a supplied uniform.
"><objectives><objective index="0" id="I0004-1"/></objectives><corr_resps><corr_resp index="0" pattern="false"/></corr_resps></interaction><interaction index="4" id="I0005-1" timestamp="2007-07-10T14:14:11" weighting="1" type="true-false" latency="PT2S" learner_response="false" result="incorrect" description="Uniform shirts should be tucked in and belts worn all times.
"><objectives><objective index="0" id="I0005-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction></interactions>
Does SQL 2000 have an XML datatype? I don't think so.
I think what it does support is the OPENXML row set provider so for instance to read out the attributes of the interaction elements you could use the following:
Code Snippet
DECLARE @.x nvarchar(2000);
SET @.x = '<interactions><interaction index="0" id="I0001-1" timestamp="2007-07-10T14:14:00" weighting="1" type="true-false" latency="PT3S" learner_response="true" result="correct" description="Tire center employees should know where to access."><objectives><objective index="0" id="I0001-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="1" id="I0002-1" timestamp="2007-07-10T14:14:02" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="incorrect" description=" does not sell used tires or any tire that has previously been mounted on a rim and driven on."><objectives><objective index="0" id="I0002-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="2" id="I0003-1" timestamp="2007-07-10T14:14:05" weighting="1" type="choice" latency="PT3S" learner_response="Taken" result="incorrect" description="Tire center supplies should not be:"><objectives><objective index="0" id="I0003-1"/></objectives><corr_resps><corr_resp index="0" pattern="All_apply"/></corr_resps></interaction><interaction index="3" id="I0004-1" timestamp="2007-07-10T14:14:08" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="correct" description="Employees have the option to wear a supplied uniform.
"><objectives><objective index="0" id="I0004-1"/></objectives><corr_resps><corr_resp index="0" pattern="false"/></corr_resps></interaction><interaction index="4" id="I0005-1" timestamp="2007-07-10T14:14:11" weighting="1" type="true-false" latency="PT2S" learner_response="false" result="incorrect" description="Uniform shirts should be tucked in and belts worn all times.
"><objectives><objective index="0" id="I0005-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction></interactions>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML (@.iDoc, '/interactions/interaction', 1)
WITH (
[index] int,
[id] nvarchar(10),
[timestamp] datetime,
[weighting] int,
[type] nvarchar(10),
[latency] nvarchar(5),
[learner_response] nvarchar(5),
[result] nvarchar(10),
[description] nvarchar(30),
);
EXEC sp_xml_removedocument @.iDoc;
|||Thanks for the reply. I've looked at OPENXML, but a bit confused by it when the data alread resides in a field. The column name is 'CntItmPrgs_Interactions'. Do I set @.x = the column name? i.e. Set @.x = CntItmPrgs.CntItmPrgs_Interactions|||Yes, I think you need to use
SET @.x = (SELECT columnname FROM tablename WHERE somecondition)
|||Okay, this is what I have:
DECLARE @.x nvarchar(2000);
SET @.x =(Select CntItmPrgs_Interactions from CntItmPrgs Where CntItmPrgs_PK = 406560)
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML (@.iDoc, '/interactions/interaction', 1)
WITH (
[index] int,
[id] nvarchar(10),
[timestamp] datetime,
[weighting] int,
[type] nvarchar(10),
[latency] nvarchar(5),
[learner_response] nvarchar(5),
[result] nvarchar(10),
[description] nvarchar(30))
EXEC sp_xml_removedocument @.iDoc;
But, I'm getting this error:
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Any ideas?
|||I don't have SQL Server 2000 and don't have enough experience with it to answer without testing. Maybe someone else can help if you provide details on the type of the CntItmPrgs_Interactions column.sqlRetrieving XML data using OpenXML
Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int --document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.Hello shriram2977,
> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:
> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int --document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.Hello shriram2977,
> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:
> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Retrieving XML data using OpenXML
Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int--document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.
Hello shriram2977,
> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:
> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int--document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.
Hello shriram2977,
> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:
> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Retrieving XML Data using FOR XML AUTO into sql variable
hello guru's
I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?
Declare @.Message varchar(200)
SELECT col1,col2 from table1 FOR XML AUTO
I need to put result of above query to @.Message variable.
I have tried with following but failed ......
SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO
SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'
can anyone suggest me the solution to this .....
Thanks
Which version of SQL Server are you using -- 2000 or 2005?
|||Parentheses should help e.g.
Code Snippet
SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);
|||Thanks Kent for pointing me out,I am using SQL server 2000 Version 8.0(SP4)
I have updated the post.
Thanks
|||Hello Martin,
Parenthesis does not helped ...It is giving syntax error.
Thanks for reply
|||
Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.
|||Hi,
The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005
Retrieving XML Data using FOR XML AUTO into sql variable
hello guru's
I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?
Declare @.Message varchar(200)
SELECT col1,col2 from table1 FOR XML AUTO
I need to put result of above query to @.Message variable.
I have tried with following but failed ......
SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO
SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'
can anyone suggest me the solution to this .....
Thanks
Which version of SQL Server are you using -- 2000 or 2005?
|||Parentheses should help e.g.
Code Snippet
SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);
|||Thanks Kent for pointing me out,I am using SQL server 2000 Version 8.0(SP4)
I have updated the post.
Thanks
|||Hello Martin,
Parenthesis does not helped ...It is giving syntax error.
Thanks for reply
|||
Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.
|||Hi,
The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005
retrieving XML data from table
Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql
retrieving XML data from table
Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
Retrieving XML Data
I know that I can retrieve table data in XML format, but is there a way to
retrieve data that is STORED as XML in a column as if it was relational
data?Michael Bray wrote:
> I know that I can retrieve table data in XML format, but is there a way to
> retrieve data that is STORED as XML in a column as if it was relational
> data?
Use the nodes method on the XML column, see BOL:
<URL:http://msdn2.microsoft.com/en-us/library/ms188282.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
retrieve data that is STORED as XML in a column as if it was relational
data?Michael Bray wrote:
> I know that I can retrieve table data in XML format, but is there a way to
> retrieve data that is STORED as XML in a column as if it was relational
> data?
Use the nodes method on the XML column, see BOL:
<URL:http://msdn2.microsoft.com/en-us/library/ms188282.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
Retrieving varchar(max) and varbinary(max) fields with VFP9
I have a test database that contains a varbinary(max) field and a varchar(max) field.
when I do a
Select * from test where id = xx
I get the expected results if my connection string uses
'driver=SQL Server;Server'
but these two fields return no data if I use
'driver=SQL Native Client'
the other fields in the record come back with no problems.
Is there anything special I need to do to retrieve these types of fields?
sorry, typo:
The first driver of course it just
'driver= SQL Server'
Retrieving values from dynamic SQL
Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL?
Code Example
Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)
i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.
Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...
can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):
DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT
Terrisql
Retrieving values from a subreport to Body of Parent Report
Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.
Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.
Thanks,
Garick
I want to do something similar.
I want the value of the amount of records retrieved in the sub report.
The table row that the sub report is in needs to be hidden if the value is not greater than 1.
Can it be done?
|||Jabuka
I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.
Hope it helps you
|||Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.
The only problem with this approach is concurrency as you are using a static variable.
Shyam
retrieving user's permissions for each table
Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via SQLMonster.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>> Hi ,
>[quoted text clipped - 10 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via SQLMonster.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>> Hi ,
>[quoted text clipped - 10 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
retrieving user's permissions for each table
Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1
Retrieving User-Defined Member Properties using PROPERTIES keyword
I am using an example from ‘SQL Server 2005 Books Online’, which explain how to retrieve User-Defined Member Properties.
Using the PROPERTIES Keyword to Retrieve User-Defined Member Properties:
DIMENSION PROPERTIES [Dimension.]Level.<Custom_Member_Property>
The PROPERTIES keyword appears after the set expression of the axis specification. For example, the following MDX query the PROPERTIES keyword retrieves the List Price and Dealer Price user-defined member properties and appears after the set expression that identifies the products sold in January:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS
DIMENSION PROPERTIES
Product.Product.[List Price],
Product.Product.[Dealer Price]ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
After running the above MDX query, I don’t see any [List Price] or [Dealer Price] and the result is exactly like running the following MDX query:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
How can I retrieve User-Defined Member Properties?
Thanks,
Yones
I found the problem, which is related to the way data is returned after execution of an MDX query. It is returned differently in Analysis Services 2000 and 2005. For example using an XMLReader, elements names are returned as follow:
AS 2000:
clXmlReader.Name:"List Price"
clXmlReader.value:"List Price value"
clXmlReader.Name:"Dealer Price"
clXmlReader.value:"Dealer Price value"
AS 2005:
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_Product_x005D_._x005B_Product_x005D_._x005B_ List Price _x005D_"
clXmlReader.value:"List Price value"
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Dealer Price _x005D_"
clXmlReader.value:"Dealer Price value"
Retrieving User/Role Privileges - How ?
Hi,
I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.
Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?
I have researched SQL-DMO, but didn't find what I'm looking for.
Any idea ?
MikeTry the next command in query analyzer
sp_helprotect null, 'username' to get the rights
and
sp_helpuser to get the roles of an usersql
I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.
Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?
I have researched SQL-DMO, but didn't find what I'm looking for.
Any idea ?
MikeTry the next command in query analyzer
sp_helprotect null, 'username' to get the rights
and
sp_helpuser to get the roles of an usersql
Subscribe to:
Posts (Atom)