Hi gurus,
I need to be able to export a table to an xml file on the 1st of every month. How can I accomplish this using SQL.
Thanks!
SQL is structured query language, it has nothing to do with scheduling.
If your using SQL Server 2000 or later, you can schedule a "job" to run at intervals, using a utility called DTS. It also has the ability to create xml files from your database tables.
Mirko
There is a nice article about XML fields and SQL server.
http://www.developer.com/db/article.php/3565996
However there is a problem on that article I'll paste the insert statements here:
INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')
INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')
INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')
Thank you!
Mirko
Mirko
There is a nice article about XML fields and SQL server.
http://www.developer.com/db/article.php/3565996
However there is a problem on that article I'll paste the insert statements here:
INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'<?xml version="1.0" ?>
<Document Name="Poem">
<Author>Bruce</Author>
<Text>The cat/is flat.</Text>
</Document>')
INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'<?xml version="1.0" ?>
<Document Name="Code">
<Author>Hammurabi</Author>
<Text>An eye for an eye, a tooth for a tooth.</Text>
</Document>')
INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'<?xml version="1.0" ?>
<Document Name="Jack and Jill">
<Author>Mother Hubbard</Author>
<Text>Jack and Jill/went up the hill.</Text>
</Document>')
Thank you!
Mirko
Hi
I have a T-SQL like :
Code Snippet
select * from customers for xml PATH , ROOTand how to save the result to a xml file .
Refer to Books Online about using BCP.
Look in Books Online, Topic: 'BCP Utility'
Hi ,
I want to return the xml datatype values in the output of my query.
I am querrying on one table which has xml datatype by selelcting the column in select clause and would like to get the values in output.
How do i get output with out/with including the column name in group by clause?
Can some one help me here.
--Smita.
You have a XML column and you want to break the XML as columns ? is this what you want ?|||Rick,
I have a coulmn with xml datatype and want to get entire column value in one output.
-Smitha.
|||Something like this ?
declare @.x xml
set @.x='<Root>
<Node>
<Node name="S.No">1</Node>
<Node name="S.Level">1</Node>
<Node name="S.FName">Data1</Node>
<Node name="S.LName">Data2</Node>
<Node name="Sl #" />
</Node>
<Node>
<Node name="S.No">2</Node>
<Node name="S.Level">2</Node>
<Node name="S.FName">Data22</Node>
<Node name="S.LName">Data33</Node>
<Node name="Sl #" />
</Node>
</Root>'
select cast(r.query('for $data in ./Node return data($data) ') as varchar(max)) as [Column]
from @.x.nodes('/Root/Node') as X(r)
*damn*I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text(),
for $s in self::node()
return $s//SourcesType/text()
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
Asaf:
I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.
Code Snippet
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x
/*
SourcesID SourcesType
-- -
57341 50
*/
Hi there,
I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.
Thanks very much.
|||It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.
FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)
then you can apply the value method to get two columns in the result.
|||
Code Snippet
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)
/*
a b
-
57341 50
*/
I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text(),
for $s in self::node()
return $s//SourcesType/text()
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
Asaf:
I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.
Code Snippet
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x
/*
SourcesID SourcesType
-- -
57341 50
*/
Hi there,
I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.
Thanks very much.
|||It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.
FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)
then you can apply the value method to get two columns in the result.
|||
Code Snippet
DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)
/*
a b
-
57341 50
*/
Can anyone please help me in solving this.
Thanks a lot for your help and time.
PavelPavel (p.golobokov@.ausbulk.com.au) writes:
> I have SQL 2000 and need to retrieve fairly large amout of data (~
> 50.000 characters) in XML format and then insert it into the field of
> the text type.
> As 'FOR XML' can't be used with either local variables, INSERT INTO or
> SELECT INTO this makes "XML support" quite useless in many aspects.
You can try:
INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT ... FOR XML')
Where LOCALSVR has been created as
EXEC sp_addlinkedserver
@.server = 'LOCALVR',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'LocalServer'
That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)
If this does not work out, you will have a find a client to pick up the
XML and send it back.
In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Hi,
I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:
User searches for all entries with Forename = John; Results are listed in a gridview as per expected.
User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format
<records>
<record>
<Forename>John</Forename>
<Surname>Smith</Surname>
<Email>j.smith@.blah.com</Email>
<Ext>1234</Ext>
<DeptList>History</DeptList>
</record>
</records>
I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:
CREATE
PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML@.Forename
varchar(50),@.Surname
varchar(50),@.Ext
varchar(4),@.DeptList
varchar(50)AS
SELECT
Forename, Surname, Email, Ext, DeptListFROM
dbo.recordWHERE
ForenameLIKECOALESCE(@.Forename,Forename)ANDSurname
LIKECOALESCE(@.Surname,Surname)ANDExt
LIKECOALESCE(@.Ext,Ext)ANDDeptList
LIKECOALESCE(@.DeptList,DeptList)FOR
XMLAUTO,ELEMENTSIf someone could be kind enough to help me out with this, I'd be really grateful.
Many thanks,
Tom
Hi tomosap,
Yes, this is a good way to get that.
Another way is to fill everything to run the query and fill data into a DataSet. You will be able to get the DataSet's data in XML format by calling DataSet.WriteXml() method.
Both these methods are good.
HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!
|||Hi,
Thanks for your reply - I'm afraid that my needs have slightly changed since this was posted. I now have an XML file which I transform using XSL so it displays all the data in a gridview. What I want to do is to have a search form so that I can search the data that's in the gridview. I.e.
Name: [INPUT]
Results in Gridview...
User enters name in input, clicks search, then all matches in Name column on the gridview is displayed. I've successfully managed to do this with a basic SQL database using the object source control and passing paramaters, but I don't know how to do this when the data source is XML.
All help would be appreciated.
Thanks,
Tom
