SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML and SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yommy1831
Starting Member

USA
10 Posts

Posted - 07/22/2013 :  10:24:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 07/22/2013 :  10:32:41  Show Profile  Reply with Quote
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

USA
10 Posts

Posted - 07/22/2013 :  11:06:22  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 07/22/2013 :  12:07:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/22/2013 :  13:51:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How does the XML document look like?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000