Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Monday, March 19, 2012
how to get sql output in an XML file?
Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish
Friday, March 9, 2012
How to get query result in XML format using osql.exe (command line)
Hi.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.
How to get parameters in XSLT from template files...
I'd like to get the HTTP parameters to a form in both the XML template and
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
PerWe cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
PerWe cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>
How to get parameters in XSLT from template files...
I'd like to get the HTTP parameters to a form in both the XML template and
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
Per
We cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
Per
We cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>
Wednesday, March 7, 2012
How to get node name in SQL 2005 nodes query? thanks
for example,xml like
'<name>test</name><age>10</age>'
select ? from @.xml.nodes('/*')
how to get the node tag name like name and age?
thanks
You can try the following code:
XmlDocument doc =new XmlDocument(); doc.Load("yourpath.xml");//Select and display the value of all the ISBN attributes. XmlElement root = doc.DocumentElement;if (root.HasChildNodes) {for (int i = 0; i < root.ChildNodes.Count; i++) { Response.Write(root.ChildNodes[i].Name); } } Hope it helps.|||Sorry. I didn't make it clear. What I want to know is how to get nodename in the SQL 2005 XML query.
Thanks anyway.
|||Hi
I triedSELECT [column1].query('data(/root/*)') FROM [xml_test] it returned the data value of the nodes.
But when I triedSELECT [column1].query('node-name(/root/*)') FROM [xml_test]
It produced an error said node-name which was defined in xpath was not supported.
Hope others have a good idea.
Sunday, February 19, 2012
How to get hierarchical xml based of multiple tables xml columns
Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..
Subscribe to:
Posts (Atom)