Using XQuery 1

[sds814]

DECLARE @myXML XML
SET @myXML = '<SampleXML>
    <Product EffectiveDate="2007-01-01" ProductCode="030" />
    <Product EffectiveDate="2008-01-03" ProductCode="030" />
    <Product EffectiveDate="2007-01-10" ProductCode="030" />
    <Product EffectiveDate="2007-01-20" ProductCode="090" />
    <Product EffectiveDate="2007-01-01" ProductCode="090" />
    <Product EffectiveDate="2005-10-01" ProductCode="090" />
    <Product EffectiveDate="2011-01-21" ProductCode="0BA" />
    <Product EffectiveDate="2007-01-01" ProductCode="0BA" />
</SampleXML>'

select @myXML.value('(/SampleXML/Product/@EffectiveDate)[1]', 'datetime')

Is there any way that I can get all the values for Effective Date? With the query I have I am passing [1]

I'd like to get all the Product Code values also.

 

[gmmastros]

There's likely to be multiple ways to do this. This is just one of them.

DECLARE @myXML XML
SET @myXML = '<SampleXML>
    <Product EffectiveDate="2007-01-01" ProductCode="030" />
    <Product EffectiveDate="2008-01-03" ProductCode="030" />
    <Product EffectiveDate="2007-01-10" ProductCode="030" />
    <Product EffectiveDate="2007-01-20" ProductCode="090" />
    <Product EffectiveDate="2007-01-01" ProductCode="090" />
    <Product EffectiveDate="2005-10-01" ProductCode="090" />
    <Product EffectiveDate="2011-01-21" ProductCode="0BA" />
    <Product EffectiveDate="2007-01-01" ProductCode="0BA" />
</SampleXML>'

Declare @iDoc Int

exec sp_xml_preparedocument @iDoc OUTPUT, @myXML

Select *
From   OpenXML(@iDoc, '/SampleXML/Product', 3)
With   (EffectiveDate DateTime,
		ProductCode VarChar(3))

Exec sp_xml_removedocument @iDoc

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here's another way.

DECLARE @myXML XML
SET @myXML = '<SampleXML>
    <Product EffectiveDate="2007-01-01" ProductCode="030" />
    <Product EffectiveDate="2008-01-03" ProductCode="030" />
    <Product EffectiveDate="2007-01-10" ProductCode="030" />
    <Product EffectiveDate="2007-01-20" ProductCode="090" />
    <Product EffectiveDate="2007-01-01" ProductCode="090" />
    <Product EffectiveDate="2005-10-01" ProductCode="090" />
    <Product EffectiveDate="2011-01-21" ProductCode="0BA" />
    <Product EffectiveDate="2007-01-01" ProductCode="0BA" />
</SampleXML>'

Select Product.value('@EffectiveDate[1]','DateTime') EffectiveDate,
       Product.value('@ProductCode[1]','varchar(3)') As ProductCode
From   @myXML.nodes('SampleXML/Product') Product([Product])

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks George! The second way seems like it's using SQL Server 2005 syntax.

 

[gmmastros]

Yes. The first method will work on SQL2005 (if you change the XML data type to varchar). The second way will work with SQL2005 and newer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom