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

Gottfried Lesigang · Accepted Answer · 2017-03-08 07:59:53Z

FROM OPENXML is outdated and should not be used anymore (rare exceptions exist)

Try to use the up-to-date XML-type-methods:

SELECT p.value(N'@TUT',N'bit') AS TUT
      ,p.value(N'@ID',N'nvarchar(max)') AS ID
      ,p.value(N'(Identifier/text())[1]',N'nvarchar(max)') AS Identifier
      ,p.value(N'(ProductItemCode/text())[1]',N'int') AS ProductItemCode
      ,p.value(N'(Qty/text())[1]',N'int') AS Qty
      ,p.value(N'(NetWeight/text())[1]',N'decimal(14,4)') AS NetWeight
      ,p.value(N'(SerialNumber/text())[1]',N'int') AS SerialNumber
      ,p.value(N'(ECertItemNumber/text())[1]',N'int') AS ECertItemNumber
      ,p.value(N'(Markets/@Type)[1]',N'nvarchar(max)') AS Markets_Type
      ,m.value(N'text()[1]',N'nvarchar(max)') AS Markets_Market
      ,p.value(N'(Attribute[@Name="PackDate"]/text())[1]',N'datetime') AS PackDate
      ,p.value(N'(Attribute[@Name="PlantID"]/text())[1]',N'int') AS PlantID
      ,p.value(N'(Attribute[@Name="SlgrDate"]/text())[1]',N'datetime') AS SlgrDate
FROM @xml.nodes(N'Transfer/Products/Product') AS A(p)
CROSS APPLY a.p.nodes(N'Markets/Market') AS B(m);

The result

+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+
| TUT | ID                   | Identifier           | ProductItemCode | Qty | NetWeight | SerialNumber | ECertItemNumber | Markets_Type | Markets_Market | PackDate                | PlantID | SlgrDate                |
+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+
| 1   | 38319223176264031724 | 38319223176264031724 | 83192           | 1   | 23.1000   | 317          | 2               | ECERT        | EU             | 2016-09-20 00:00:00.000 | 124     | 2016-09-19 00:00:00.000 |
+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+
| 1   | 38319223176264031724 | 38319223176264031724 | 83192           | 1   | 23.1000   | 317          | 2               | ECERT        | US             | 2016-09-20 00:00:00.000 | 124     | 2016-09-19 00:00:00.000 |
+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+
| 1   | 28319219766306010024 | 28319219766306010024 | 83192           | 1   | 19.7000   | 100          | 2               | ECERT        | EU             | 2016-11-01 00:00:00.000 | 124     | 2016-10-31 00:00:00.000 |
+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+
| 1   | 28319219766306010024 | 28319219766306010024 | 83192           | 1   | 19.7000   | 100          | 2               | ECERT        | US             | 2016-11-01 00:00:00.000 | 124     | 2016-10-31 00:00:00.000 |
+-----+----------------------+----------------------+-----------------+-----+-----------+--------------+-----------------+--------------+----------------+-------------------------+---------+-------------------------+

Hint: If you do not need the <Markets> as 1:n-relation (here doubling your resultset!), just remove the CROSS APPLY and the line starting with m.value.

Thank you..i'll give this one a go as well..really appreciate the time and effort :)

Mikael Eriksson · Accepted Answer · 2017-03-08 06:48:25Z

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"]'
    )

answered Mar 8, 2017 at 6:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

Harry Over a year ago

Worked a treat.. thank you very much. really appreciate the time and effort.

Collectives™ on Stack Overflow

Get Value from XML attribute in SQL Server 2008 Using OPENXML

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related