Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML and SQL

Author  Topic 

yommy1831
Starting Member

10 Posts

Posted - 2013-07-22 : 10:24:29
how can i adjust the code below to:

Add a second parameter to my the code that specifies qualifier for table from which to load documents and
defaults to value given by a new "current epoch" function.

The code:


create PROCEDURE [dbo].[sp_ExtractBlankData]
@guid VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xpath VARCHAR(MAX)
DECLARE @xml XML
-- check for null guid IF (@guid IS NULL)
BEGIN
RAISERROR('Invalid parameter: @guid cannot be NULL.', 18, 0)
RETURN
END

SELECT @xml = xpaths.query('declare default element namespace "http://www"; //GUID[. eq sql:variable("@guid")]/..//path/text()')
FROM fas.dbo.[Directory]
SET @xpath = CONVERT(VARCHAR(MAX), @xml)

SET @xpath = REPLACE(@xpath,'/faculty-activities-taxonomy/','')
SET @xpath = @xpath+'.xml'
SET @xpath = REPLACE(@xpath,'/','.')

select blankFile from fas.dbo.BlankData where fileName = @xpath
END
GO

--guid - it is a string that identifies an XML document to be extracted from DB Schema

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 10:32:41
quote:
Originally posted by yommy1831

how can i adjust the code below to:

Add a second parameter to my the code that specifies qualifier for table from which to load documents and
defaults to value given by a new "current epoch" function.

The code:


create PROCEDURE [dbo].[sp_ExtractBlankData]
@guid VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xpath VARCHAR(MAX)
DECLARE @xml XML
-- check for null guid IF (@guid IS NULL)
BEGIN
RAISERROR('Invalid parameter: @guid cannot be NULL.', 18, 0)
RETURN
END

SELECT @xml = xpaths.query('declare default element namespace "http://www"; //GUID[. eq sql:variable("@guid")]/..//path/text()')
FROM fas.dbo.[Directory]
SET @xpath = CONVERT(VARCHAR(MAX), @xml)

SET @xpath = REPLACE(@xpath,'/faculty-activities-taxonomy/','')
SET @xpath = @xpath+'.xml'
SET @xpath = REPLACE(@xpath,'/','.')

select blankFile from fas.dbo.BlankData where fileName = @xpath
END
GO

--guid - it is a string that identifies an XML document to be extracted from DB Schema

Can you explain (with an example) what you meant by "qualifier for table" ?
Go to Top of Page

yommy1831
Starting Member

10 Posts

Posted - 2013-07-22 : 11:06:22
Hello,

The concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of blank documents from different tables in a set of related tables: e.g.., BlankData_201308, BlankData_201309...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:07:12
Isn't there a "schema version" attribute in the xml document?
If so (which there should be), extract the schema version and apply a lot of IF's.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

yommy1831
Starting Member

10 Posts

Posted - 2013-07-22 : 12:54:31
there is a schema. I dpnt know how to fix the situation
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 13:51:48
How does the XML document look like?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -