Get Value from XML attribute in SQL Server 2008 Using OPENXML

Question

I'm trying to extract a value from my XML and seem to be struggling. Hope someone can help

here is my XML

       '<Transfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Products>
        <Product TUT="true" ID="38319223176264031724">
            <Identifier>38319223176264031724</Identifier>
            <ProductItemCode>83192</ProductItemCode>
            <Qty>1</Qty>
            <NetWeight>23.100</NetWeight>
            <GrossWeight>23.684</GrossWeight>
            <SerialNumber>317</SerialNumber>
            <ECertItemNumber>2</ECertItemNumber>
            <Markets Type="ECERT">
                <Market>EU</Market>
                <Market>US</Market>
            </Markets>
            <Attribute Name="PackDate">2016-09-20T00:00:00</Attribute>
            <Attribute Name="PlantID">124</Attribute>
            <Attribute Name="SlgrDate">2016-09-19T00:00:00</Attribute>
        </Product>
        <Product TUT="true" ID="28319219766306010024">
            <Identifier>28319219766306010024</Identifier>
            <ProductItemCode>83192</ProductItemCode>
            <Qty>1</Qty>
            <NetWeight>19.700</NetWeight>
            <GrossWeight>20.284</GrossWeight>
            <SerialNumber>100</SerialNumber>
            <ECertItemNumber>2</ECertItemNumber>
            <Markets Type="ECERT">
                <Market>EU</Market>
                <Market>US</Market>
            </Markets>
            <Attribute Name="PackDate">2016-11-01T00:00:00</Attribute>
            <Attribute Name="PlantID">124</Attribute>
            <Attribute Name="SlgrDate">2016-10-31T00:00:00</Attribute>
        </Product>
</Products>
    </Transfer>'

What I want to extract are the Identifier, ProductItemCode, NetWeight, GrossWeight, AND the Attribute Values of PackDate and SlgrDate.

I can easily get all of the fields EXCEPT for Attribute Values of PackDate and SlgrDate

Here is my code for the fields

if OBJECT_ID('tempdb..#XmlImportTest') is not null
drop table #XmlImportTest


CREATE TABLE #XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(3000)

SELECT @xmlFileName = 'K:\Upload\CSNXML\WaybillXml.xml'


--– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


select @xml = (SELECT xml_data from #XmlImportTest)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT Identifier as barcode,ProductItemCode as standpack,SerialNumber, NetWeight netwt_ind, GrossWeight grosswt_ind 
FROM OPENXML (@hDoc, '/Transfer/Products/Product',2)
WITH (Identifier varchar(80),
ProductItemCode varchar(10),
SerialNumber varchar(48),
NetWeight decimal(13,2),
GrossWeight decimal(13,2)
) 


exec sp_xml_removedocument @hDoc

the xml file contains the same as the sample xml provided Now I have no idea how to get the value out of the Attributes for each product.

I am running this in SQL SERVER 2008

Answer 1

Use the optional ColPattern to specify the XPath to the node you want.

FROM OPENXML (@hDoc, '/Transfer/Products/Product',2)
WITH (
     Identifier varchar(80),
     ProductItemCode varchar(10),
     SerialNumber varchar(48),
     NetWeight decimal(13,2),
     GrossWeight decimal(13,2),
     PackDate datetime 'Attribute[@Name = "PackDate"]',
     PlantID int 'Attribute[@Name = "PlantID"]',
     SlgrDate datetime 'Attribute[@Name = "SlgrDate"]'
    )