Category Archives: Stored Procedures

July 28, 2011

Shred XML Attributes in SQL 2008

By sqlches

Scenario:   Need to shred attribute values out of an XML Variable in SQL 2008

Solution:  Use XQuery and Nodes/Value to pull out the needed values.

Snippet:

declare @XMLMessage xml
set @XMLMessage = '<XMLMessageName xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
 IsResend = "false" StoreID= "195" PartnerID = "-1">
    <LogTime StartDateTime = "2011-06-24T08:47:40-07:00">
        <SalesEvents>
            <SaleEvent
                PartnerID = "-1"
                Comment = ""
                UPC = ""
                Keyword = ""
                Coop = ""
                Category= ""
                Region= "-1"
                InsertionType = "Normal"
                ClerkNumber= "0010068-001"
                Client = ""
                ISCI = ""
                Manufacturer = "Justin"
                Item= "Stuff"
                SaleType= "Credit"
                SaleNumber = "325077"
                BreakID = "-1"
                Sequence = "1"/>
        </SalesEvents>
    </LogTime>
</XMLMessageName >'

select
	ClerkNumber= t.o.value('@ClerkNumber', 'varchar(50)')
	, StoreID= t.o.value('../../../@StoreID', 'integer')

from @XMLMessage.nodes('XMLMessageName /LogTime/SalesEvents/SaleEvent') as t(o)
where t.o.value('@Sequence', 'integer') = 1

Results:

ClerkNumber                                        StoreID            
-------------------------------------------------- -------------------
0010068-001                                        195                
(1 row(s) affected)
Advertisement

Leave a comment   |  tags: , , | posted in Stored Procedures, T-SQL, XML

July 15, 2011

No column info from stored procedure

By sqlches

Scenario:  Using a stored procedure as the source in an OLE_DB Source for SSIS,  the package can’t detect the output of the stored proc.

Error:  “No column information was returned by the SQL command”

Solution:  Add a no-op statement at the beginning of the stored procedure to set up the pipeline.

Snippet:

If Object_Id('[dbo].[GenMetadata]', 'P') Is Not Null
Drop Procedure [dbo].[GenMetadata]
Go
Create Procedure [dbo].[genMetAdAta]
As
Set NoCount On
/*********** This is what to add ***********************/   If 1 = 0
     Begin
         -- Publish metadata
         Select
           Cast(Null As Int)           As Id
           , Cast(Null As Nchar( 10 )) As [Name]
           , Cast(Null As Nchar( 10 )) As SirName
     End

/*********** end add ***********************/
 -- Do real work starting here
Create Table #Test
 (
 [id]        [Int] Null ,  [Name]    [nChar]( 10 ) Null ,
[SirName] [nChar]( 10 ) Null )

 Insert Into #Test
 Select '1', 'A', 'Z'
 Union All
 Select '2', 'b', 'y'
 Select
 Id, [Name], SirName
 From   #Test
 Drop Table #Test

Return
Go

Source: jaegd – SQL Server Forums

Leave a comment   |  tags: , , | posted in OLE_DB Source, SSIS, Stored Procedures