XML to SQL table (epcis:EPCISDocument)

Question

Could you help me here, please? I need to do a query with an XML file, however, it has something different because it has the epcis: at the beginning of the XML document.

So, if I try to do the query with epcis: and dts: the result is:

Msg 2229, Level 16, State 1, Line 38.
XQuery [nodes()]: The name "epcis" does not denote a namespace.

And if I try to do the query without epcis: and dts:, the result is in blank.

BEGIN
    DECLARE @archivo XML
    SET @archivo = (
        '<?xml version="1.0" encoding="utf-8"?>
            <epcis:EPCISDocument xmlns:dts="urn:dts:extension:xsd" schemaVersion="1.2" creationDate="2021-06-30T07:29:32.6511940Z"
                xmlns:epcis="urn:epcglobal:epcis:xsd:1">
                <EPCISBody>
                    <EventList>
                        <ObjectEvent>
                            <eventTime>2021-06-30T07:29:32</eventTime>
                            <eventTimeZoneOffset>+02:00</eventTimeZoneOffset>
                            <action>OBSERVE</action>
                            <bizStep>code</bizStep>
                            <dts:epcItemList>
                                <item>
                                    <epc>123456789</epc>
                                    <code>123456789zz</code>
                                </item>
                                <item>
                                    <epc>9687654321</epc>
                                    <code>9687654321zz</code>
                                </item>
                                <item>
                                    <epc>147258369</epc>
                                    <code>147258369zz</code>
                                </item>
                            </dts:epcItemList>
                        </ObjectEvent>
                    </EventList>
                </EPCISBody>
            </epcis:EPCISDocument>'
    )


    SELECT 
        patch.r.value('(epc)[1]', 'varchar(100)') as [epc],
        patch.r.value('(code)[1]', 'varchar(100)') as [code]
    FROM  
        @archivo.nodes('EPCISDocument/EPCISBody/EventList/ObjectEvent/epcItemList/item') AS patch(r)
END 
GO

I need to export the information as a SQL table per item, like this:

| epc        | code         |
| --------   | ------------ |
| 123456789  | 123456789zz  |
| 9687654321 | 9687654321zz |

enter image description here

Thank you so much Juan

score 1 · Answer 1 · answered Aug 19 '21 at 21:48

You need to declare your namespaces. The namespace aliases present in the XML are not relevant, you can use any alias you like (that's the bit after AS in the declaration).

Also, text() is more performant when used in .value

WITH XMLNAMESPACES (
  'urn:epcglobal:epcis:xsd:1' AS epcis,
  'urn:dts:extension:xsd' AS dts
)
    SELECT 
        patch.r.value('(epc/text())[1]', 'varchar(100)') as [epc],
        patch.r.value('(code/text())[1]', 'varchar(100)') as [code]
    FROM  @archivo.nodes('epcis:EPCISDocument/EPCISBody/EventList/ObjectEvent/dts:epcItemList/item') as patch(r);

SQL Fiddle

XML to SQL table (epcis:EPCISDocument)

1 Answers1