Read XML File with Namespaces using xmlnamespaces in SQL Server 2008

Question

After several days of tweaking i still couldn't get this right. I am trying to read a xml file with lots of namespaces, inserting particular node values to a different table.

XML

<ArrayOfCatalogItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <CatalogItem Version="1">
    <Container xmlns="http://3ecompany.com/webservices/catalogitemxml">
      <ContainerType>Unknown</ContainerType>
      <MarkedForRetail xsi:nil="true" />
    </Container>
    <Documents CultureCode="en" Elink="https://3eonline.com/ImageServer/ImageViewer.aspx?id=3Q%2ffAR8ne%2fvPh6syVnSymkS%2bBDo8OjmbVocxRCMEgeEuoz0WYqIq%2bpG3%2b3wu9B2vvARePPfTWFBb0hg91%2fRYfNzA43I%2baZTLYlibHjHcCDI%3d" Format="Msds" DocumentType="Sds" xmlns="http://3ecompany.com/webservices/catalogitemxml" />
    <Documents CultureCode="en" Elink="https://3eonline.com/ImageServer/ImageViewer.aspx?id=3Q%2ffAR8ne%2fvPh6syVnSymniD9dyO5cXo%2bPmAACkW7RMmVjYMZVxizRxXLlqcjbNGgyhjsG5gzhZK9bibPB5EPg%3d%3d" Format="ClientAttachment" DocumentType="Attachment" xmlns="http://3ecompany.com/webservices/catalogitemxml" />
    <IsHazardous xmlns="http://3ecompany.com/webservices/catalogitemxml">true</IsHazardous>
    <ManufacturerName xmlns="http://3ecompany.com/webservices/catalogitemxml">Sigma-Aldrich</ManufacturerName>
    <Msds xmlns="http://3ecompany.com/webservices/catalogitemxml">
      <Elink>https://3eonline.com/ImageServer/ImageViewer.aspx?id=3Q%2ffAR8ne%2fvPh6syVnSymqIuIP5CInA01ZbaRQ9r18HUOi1FRQqntYtr58dWAm4wO3rdUO%2bO6MamuvwN7v7fbA%3d%3d</Elink>
      <FireCodeClassification>
      <MsdsId>8342624</MsdsId>
      <Properties>
        <PhysicalState>Liquid</PhysicalState>
        <BoilingPoint>
          <Minimum xsi:nil="true" />
          <Range>EqualTo</Range>
          <Units>Celsius</Units>
          <Value>217.0000</Value>
        </BoilingPoint>
      </Properties>
      <TransportationClassificationCompleted xsi:nil="true" />
      <WasteCompleted xsi:nil="true" />
      <ExtendedSds>false</ExtendedSds>
      <TransportationExceptionClassificationCompleted xsi:nil="true" />
      <BestAvailable>false</BestAvailable>
    </Msds>
    <ProductIdentifiers xmlns="http://3ecompany.com/webservices/catalogitemxml">
      <Identifier>4197644</Identifier>
      <FlaggedForResend xsi:nil="true" />
    </ProductIdentifiers>
    <ProductName xmlns="http://3ecompany.com/webservices/catalogitemxml">(3-Aminopropyl)triethoxyeilane - A3648</ProductName>
    <ProductUid xmlns="http://3ecompany.com/webservices/catalogitemxml">4d3dc6df9cf24cc6b3ab2b6be8373858</ProductUid>
    <IsDeactivated xmlns="http://3ecompany.com/webservices/catalogitemxml">false</IsDeactivated>
    <DeactivatedDate xsi:nil="true" xmlns="http://3ecompany.com/webservices/catalogitemxml" />
  </CatalogItem>
</ArrayOfCatalogItem>

I need to fetch several values from this xml (I deleted most of the xml for readability). I wrote the sample query to fetch only Identifier and all i get is 'Null' for the column Identifier. I want to fetch ContainerType,MsdsID,PhysicalState,Minimum,

Query

DECLARE @XmlTable TABLE (XMLDATA XML)

INSERT INTO @XmlTable(XMLData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn 
FROM OPENROWSET(BULK 'C:\AAEWRXML.xml', SINGLE_BLOB) AS x;

;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema','http://3ecompany.com/webservices/catalogitemxml' as CI)
     SELECT 
     Identifier = XmlData.value('(ArrayOfCatalogItem/CatalogItem/ProductIdentifiers/CI:Identifier)[1]', 'varchar(10)'),
     From
     @XmlTable

marc_s · Accepted Answer · 2016-08-16 11:19:20Z

2

Use this:

;WITH XMLNAMESPACES ('http://3ecompany.com/webservices/catalogitemxml' as CI)
SELECT 
    CIVersion = CI.value('@Version', 'int'),
    Identifier = PID.value('(.)[1]', 'varchar(10)')
FROM
    @XmlTable
CROSS APPLY
    XMLData.nodes('/ArrayOfCatalogItem/CatalogItem') AS XT(CI)
CROSS APPLY
    CI.nodes('CI:ProductIdentifiers/CI:Identifier') AS XT2(PID)

There is no default XML namespace, and you need to apply the CI: prefix to both the <ProductIdentifiers> as well as the <Identifier> nodes. Also, your sample XML is missing a closing </FireCodeClassification> tag ....

edited Aug 16, 2016 at 11:19

answered Aug 16, 2016 at 11:06

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sakthivel Over a year ago

Mark, i might have multiple catalogItem nodes in the xml. so multiple identifers and so on. i forgot to mention it in the question.

marc_s Over a year ago

@codebrain: and do you have multiple <identifier> inside a single <catalogItem> as well?

Sakthivel Over a year ago

No only one identifier in one catalogitem. The root node ArrayofCatalogItem' will have many catalogItem node groups. For readability i added one catalogitem in the question.

marc_s Over a year ago

Updated response to handle multiple <CatalogItem> elements

marc_s Over a year ago

@codebrain: those might need to be treated as strings (varchar(10) or so)

|

Devart · Accepted Answer · 2016-08-16 11:14:32Z

1

Try to use *

DECLARE @x XML

SELECT @x = CONVERT(XML, BulkColumn) 
FROM OPENROWSET(BULK 'C:\AAEWRXML.xml', SINGLE_BLOB)

SELECT @x.value('(*:ArrayOfCatalogItem/*:CatalogItem/*:ProductIdentifiers/*:Identifier)[1]', 'VARCHAR(10)')

If i have many catalogItems in the same what should i do?

SELECT t.c.value('(*:CatalogItem/*:ProductIdentifiers/*:Identifier)[1]', 'VARCHAR(10)')
FROM @x.nodes('*:ArrayOfCatalogItem') t(c)

edited Aug 16, 2016 at 11:14

answered Aug 16, 2016 at 10:46

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

Sakthivel Over a year ago

It worked after adding 'as xml' in the from clause. It works only for the first catalogitem node set in the xml. If i have many catalogItems in the same what should i do ?

Sakthivel Over a year ago

still fetches me only the first identifier's value. output is only a single record. identifier[2] fetches the next catalogitem\ProductIdentifier\identifer value

Collectives™ on Stack Overflow

Read XML File with Namespaces using xmlnamespaces in SQL Server 2008

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related