XML import into SQL Server

Question

This is my first time working with XML files. I have been able to read the file into a table. Now I am trying to access the data elements to insert / update into ERP.

I am getting stuck using https://learn.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql as a guide.

I am only attempting a single retrieval now, trying to keep things simple: XML:

<TranscriptRequest xmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0">
  <TransmissionData>
    <DocumentID xmlns="">88895-20170227180832302-ccd</DocumentID>
    <CreatedDateTime xmlns="">2017-02-27T18:08:32.303-08:00</CreatedDateTime>
    <DocumentTypeCode xmlns="">Request</DocumentTypeCode>
    <TransmissionType xmlns="">Original</TransmissionType>
    <Source xmlns="">
      <Organization>
        <DUNS>626927060</DUNS>
        <OrganizationName>AVOW</OrganizationName>
      </Organization>
    </Source>
    <Destination xmlns="">
      <Organization>
        <OPEID>3419</OPEID>
        <OrganizationName>Charleston Southern University</OrganizationName>
      </Organization>
    </Destination>
    <DocumentProcessCode xmlns="">PRODUCTION</DocumentProcessCode>
  </TransmissionData>
  <Request>
    <CreatedDateTime xmlns="">2017-02-27T00:00:00.000-08:00</CreatedDateTime>
    <Requestor xmlns="">
      <Person>
        <Birth>
          <BirthDate>1985-01-01</BirthDate>
        </Birth>
        <Name>
          <FirstName>Chad</FirstName>
          <LastName>test2</LastName>
        </Name>
        <AlternateName>
          <FirstName>Chad</FirstName>
          <LastName>Walker</LastName>
          <CompositeName>Walker, Chad</CompositeName>
        </AlternateName>
        <Contacts>
          <Address>
            <AddressLine>10260 west st</AddressLine>
            <City>Denver</City>
            <StateProvinceCode>CO</StateProvinceCode>
            <PostalCode>80236</PostalCode>
          </Address>
          <Phone>
            <CountryPrefixCode>1</CountryPrefixCode>
            <AreaCityCode>303</AreaCityCode>
            <PhoneNumber>8152848</PhoneNumber>
          </Phone>
          <Email>
            <EmailAddress>[email protected]</EmailAddress>
          </Email>
        </Contacts>
      </Person>
    </Requestor>

My SQL:

USE TMSEPRD

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

SELECT @XML = [XMLData] FROM [dbo].[CSU_Parchment_XMLwithOpenXML]

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT BirthDate
FROM OPENXML(@hDoc, 'ROOT/Request/Requestor/Person/Birth/Birthdate')
WITH 
(
BirthDate varchar(20) '../@BirthDate'
)

EXEC sp_xml_removedocument @hDoc
GO

Birthdate remains blank. So far I have tried eliminating the ../ in front of @Birthdate, I have tried removing the @, I have tried removing Birthdate from the path.

Is your XML complete? Looks like your missing </Request></TranscriptRequest>. — STLDev
– STLDev, Commented Mar 27, 2017 at 18:31
OpenXml is outdated technology. Consider switching to Xml Data Type Methods learn.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods — Serg
– Serg, Commented Mar 27, 2017 at 18:33
You might be getting messed around by namespaces. Take a look at the WITH XMLNAMESPACES clause. — PMah
– PMah, Commented Mar 27, 2017 at 18:33
I do not know, how this XML is generated, but I'm pretty sure, that the various xmlns="" is plain wrong and might lead into troubles if you'd have to pass structure validations... — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 28, 2017 at 7:35

marc_s · Accepted Answer · 2017-03-27 18:47:17Z

Using Xml Data Type Methods

DECLARE @XML XML='<TranscriptRequest xmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0">
  <TransmissionData>
    <DocumentID xmlns="">88895-20170227180832302-ccd</DocumentID>
    <CreatedDateTime xmlns="">2017-02-27T18:08:32.303-08:00</CreatedDateTime>
    <DocumentTypeCode xmlns="">Request</DocumentTypeCode>
    <TransmissionType xmlns="">Original</TransmissionType>
    <Source xmlns="">
      <Organization>
        <DUNS>626927060</DUNS>
        <OrganizationName>AVOW</OrganizationName>
      </Organization>
    </Source>
    <Destination xmlns="">
      <Organization>
        <OPEID>3419</OPEID>
        <OrganizationName>Charleston Southern University</OrganizationName>
      </Organization>
    </Destination>
    <DocumentProcessCode xmlns="">PRODUCTION</DocumentProcessCode>
  </TransmissionData>
  <Request>
    <CreatedDateTime xmlns="">2017-02-27T00:00:00.000-08:00</CreatedDateTime>
    <Requestor xmlns="">
      <Person>
        <Birth>
          <BirthDate>1985-01-01</BirthDate>
        </Birth>
        <Name>
          <FirstName>Chad</FirstName>
          <LastName>test2</LastName>
        </Name>
        <AlternateName>
          <FirstName>Chad</FirstName>
          <LastName>Walker</LastName>
          <CompositeName>Walker, Chad</CompositeName>
        </AlternateName>
        <Contacts>
          <Address>
            <AddressLine>10260 west st</AddressLine>
            <City>Denver</City>
            <StateProvinceCode>CO</StateProvinceCode>
            <PostalCode>80236</PostalCode>
          </Address>
          <Phone>
            <CountryPrefixCode>1</CountryPrefixCode>
            <AreaCityCode>303</AreaCityCode>
            <PhoneNumber>8152848</PhoneNumber>
          </Phone>
          <Email>
            <EmailAddress>[email protected]</EmailAddress>
          </Email>
        </Contacts>
      </Person>
    </Requestor>
  </Request>
</TranscriptRequest>';

SQL query:

with xmlnamespaces('urn:org:pesc:message:TranscriptRequest:v1.0.0' as ns)
  select t.n.value('BirthDate[1]','date')
  from @XML.nodes('ns:TranscriptRequest/ns:Request/Requestor/Person/Birth') t(n);

Community · Accepted Answer · 2020-06-20 09:12:55Z

Namespaces

How is this XML generated? Is this under your control? What makes me wonder are various xmlns="" This is defining empty default namespaces over and over... Allthough there is one default NS xmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0" in the first line, which seems to be correct.

For this namespace issue there are many solutions:

Create this without the empty xmlns - if this is under your control
Use REPLACE and cut away xmlns="" (be aware of the leading blank!) on string level, before you write this into your xml typed column / variable
use a wildcard (*:) for the namespaces of the first two levels
Define a namespace alias for the default and let the empty namespaces be the default for the lower levels

Structure

Your XML seems to be plain 1:1, only 1 source, 1 destination, one reques. And only 1 person within <Request> and again only 1 Name and so on below <Person>. There is a <Contacts> element, which sounds like 1:n, but it seems to include only 1 address, 1 phone... Reading plain 1:1 is easy...

I will use a CTE with two calls in .query() for an easy approach to solve the namespace issue with a wildcard. This allows to read the rest without namespaces:

some examples how to read your elements, the rest is up to you...

WITH levels AS
(
    SELECT @xml.query(N'/*:TranscriptRequest/*:TransmissionData/*') AS td
          ,@xml.query(N'/*:TranscriptRequest/*:Request/*') AS rq
)
SELECT --TransmissionData
       td.value(N'(DocumentID/text())[1]',N'nvarchar(max)') AS DocumentID
      ,td.value(N'(CreatedDateTime/text())[1]',N'datetime') AS td_CreatedDateTime
      --more elemenst...
      ,td.value(N'(Source/Organization/DUNS/text())[1]',N'bigint') AS Source_Organization_DUNS
      --more elements...
      ,td.value(N'(Destination/Organization/OPEID/text())[1]',N'bigint') AS Destination_Organization_OPEID

      --Request
      ,rq.value(N'(CreatedDateTime/text())[1]',N'datetime') AS rq_CreatedDateTime
      --Request-Person
      ,rq.value(N'(Requestor/Person/Name/FirstName/text())[1]',N'nvarchar(max)') AS Requestor_FirstName

      --Contacts 
      ,rq.value(N'(Requestor/Person/Contacts/Address/AddressLine/text())[1]',N'nvarchar(max)') AS Requestor_AddressLine
 
FROM levels

At least at subelements xmlns="" is quite valid XML , w3.org/TR/xml-names/#defaulting , w3.org/TR/xml-names11/#defaulting. Not sure about document element.
@Serg, yes, it removes the outer namespace and lets the inner elements react as if there was no namespace. Thats why you put ns: before your first steps in your XPath, but you need no namespace for the lower levels. If the given namespace defines such a request, I'm pretty sure, that the whole lot should be covered by the same namespace...
The xml is not under my control. I am downloading the data file from a third party. The XML is suppose to be a 1:1. Thank you for pointing me in the direction of XML data type methods.
OK I have the document fully parsed. My final step is to map these values to various tables/fields in the ERP. How do I reference these parsed values? I thought it would be like #temp.DocumentID , etc.
@Gene Do I understand your I have the document fully parsed correctly, when I take, that you have a SELECT, which returns all parsed values in a derived table? Try to use SELECT col1, col2, ... INTO SomeStagingTable FROM .... This will write all data into a newly created table. With SELECT * FROM SomeStagingTable you can get hands on your data in any way you might need it.

Collectives™ on Stack Overflow

XML import into SQL Server

2 Answers 2

Comments

Namespaces

Structure

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Namespaces

Structure

10 Comments

Related