none
Trying to get SQL Server to read XML. Where am I going wrong RRS feed

  • Question

  • I've worked on SQL Server since 1.0. I even have the original box and manuals here. So I'm feeling pretty stupid right now that I can't get and XML fie to load. Here's the beginning of the XML file:

    <NewDataSet>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="DT_Community_Supervision">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Unique_ID" type="xs:string" minOccurs="0" />
                    <xs:element name="Youth_Name" type="xs:string" minOccurs="0" />
                    <xs:element name="Youth_ID" type="xs:string" minOccurs="0" />
                    <xs:element name="Monthly_Progress_Rep._Date" type="xs:dateTime" minOccurs="0" />
                    <xs:element name="Monthly_Prog_Report_Date" type="xs:dateTime" minOccurs="0" />
                    <xs:element name="General_Note_Date" type="xs:dateTime" minOccurs="0" />
                    <xs:element name="Form_Title" type="xs:string" minOccurs="0" />
                    <xs:element name="Working_Group" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <DT_Community_Supervision>
        <Unique_ID>AMIFL00000061</Unique_ID>
        <Youth_Name>Biggie TEST Smalls</Youth_Name>
        <Youth_ID>78945612</Youth_ID>
        <Monthly_Progress_Rep._Date>2019-08-06T00:00:00-04:00</Monthly_Progress_Rep._Date>
        <General_Note_Date>2019-07-19T00:00:00-04:00</General_Note_Date>
        <Form_Title>Discharge Summary</Form_Title>
        <Working_Group>Z TEST GROUP</Working_Group>
      </DT_Community_Supervision>
      <DT_Community_Supervision>
        <Unique_ID>AMIFL00000061</Unique_ID>
        <Youth_Name>Biggie TEST Smalls</Youth_Name>
        <Youth_ID>78945612</Youth_ID>
        <Monthly_Progress_Rep._Date>2019-08-06T00:00:00-04:00</Monthly_Progress_Rep._Date>
        <General_Note_Date>2019-07-19T00:00:00-04:00</General_Note_Date>
        <Form_Title>General Note to File</Form_Title>
        <Working_Group>Z TEST GROUP</Working_Group>
      </DT_Community_Supervision>

    Here is the code I wrote:

    SELECT   MY_XML.DT_Community_Supervision.query('Unique_ID').value('.', 'VARCHAR(100)'),  
     MY_XML.DT_Community_Supervision.query('Youth_Name').value('.', 'VARCHAR(100)'),  
     MY_XML.DT_Community_Supervision.query('Youth_ID').value('.', 'VARCHAR(50)'),  
     MY_XML.DT_Community_Supervision.query('Monthly_Progress_Rep._Date').value('.', 'DateTime'), 
      MY_XML.DT_Community_Supervision.query('Monthly_Prog_Report_Date').value('.', 'DateTime'),  
     MY_XML.DT_Community_Supervision.query('General_Note_Date').value('.', 'DateTime'),  
     MY_XML.DT_Community_Supervision.query('Form_Title').value('.', 'VARCHAR(100)'),  
     MY_XML.DT_Community_Supervision.query('Working_Group').value('.', 'VARCHAR(100)')
    FROM (SELECT CAST(MY_XML AS xml)	  
    FROM OPENROWSET(BULK 'myfile.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)     
    CROSS APPLY MY_XML.nodes('DT_Community_Supervision') AS MY_XML (DT_Community_Supervision);

    All I get back is:

    (No column name) (No column name) (No column name) (No column name) (No column name) (No column name) (No column name) (No column name)

    This XML looks different from examples I've seen so can someone please tell me where I'm going wrong?

    Thursday, December 12, 2019 5:11 PM

All replies

  • Hi fldbryan,

    I saved your XML as a 'e:\Temp\fldbryan.xml' file on the file system.

    The XPath expressions were off.

    I used SQL Server DATETIMEOFFSET datatype to handle dates with time zone: datetimeoffset (Transact-SQL)

    Please try the following SQL statement:

    ;WITH XmlFile (xml_data) AS
    (
       SELECT CAST(BulkColumn AS XML) 
       FROM OPENROWSET(BULK 'e:\Temp\fldbryan.xml', SINGLE_BLOB) AS XmlData
    )
    SELECT c.value('(Unique_ID/text())[1]', 'VARCHAR(20)') AS [Unique_ID]
       , c.value('(Youth_Name/text())[1]', 'VARCHAR(100)') AS [Youth_Name]
       , c.value('(Youth_ID/text())[1]', 'VARCHAR(50)') AS [Youth_ID]
       , c.value('(Monthly_Progress_Rep._Date/text())[1]', 'DATETIMEOFFSET(0)') AS [Monthly_Progress_Report_Date]
       , c.value('(General_Note_Date/text())[1]', 'DATETIMEOFFSET(0)') AS [General_Note_Date]
       , c.value('(Form_Title/text())[1]', 'VARCHAR(50)') AS [Form_Title]
       , c.value('(Working_Group/text())[1]', 'VARCHAR(50)') AS [Working_Group]
    FROM XmlFile 
    	CROSS APPLY xml_data.nodes('/NewDataSet/DT_Community_Supervision') AS t(c);

    Thursday, December 12, 2019 5:51 PM
  • It's working, albeit it slowly, but I think that's a network issue.

    Thank you

    Thursday, December 12, 2019 7:18 PM
  • Hi fldbryan,

    Glad to hear that everything is working for you now.


    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Please connect with me on the LinkedIn.

    Thursday, December 12, 2019 7:20 PM
  • You want to check for reserved characters in the column you want to convert into XML.  The one that jumps out is &  with is used to "escape" other restricted characters out of XML kind of like URL encoding.

    <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[url=https://kodi.software/]Kodi[/url] [url=https://luckypatcher.cam/]Lucky Patcher[/url] [url=https://nox.tips/]nox[/url]
    • Edited by clarc Monday, December 30, 2019 12:54 PM
    Friday, December 27, 2019 3:05 PM