CODEWITHSUNDEEP
sql-server-2012xsdsql-server-openxml
Kristi Benton
Kristi Benton

Reputation: 3

OPENXML with an embedded XSD using SQL Server 2012

I'm hoping this forum can help me. I don't know a thing about OpenXML and I have to load several files to SQL Server 2012. The source changed the file format to include an embedded xsd schema and I'm having trouble. If I modify it to start with and take out all the xsd stuff I can call the values. Most of the files are very large and not feasible to edit them all so I need to figure out how to query it without editing. Below is the query that I'm using on the file that I removed the xsd lines from. A copy of the unedited xml can be obtained here https://drive.google.com/file/d/1CIeDWTSAHFCIyz8F2zrtLCDpqe-uUeJv/view?usp=sharing

This post on the forum looks like what I need but I don't really understand it use it with my file. OPENXML with xmlns:dt

DECLARE @fileData XML

SELECT @fileData = BulkColumn 
FROM OpenRowSet(BULK 'C:\ogrid - Copy.xml',Single_blob) x;

SELECT 
    xdata.value('ogrid_cde[1]','int') ogrid_cde,
    xData.value('ogrid_nam[1]','nvarchar(255)') ogrid_name,
    xData.value('ogrid_adr_nam[1]','nvarchar(255)') ogrid_adr_name,
    xData.value('mail_stop[1]','nvarchar(255)') mail_stop,
    xData.value('line1_adr[1]','nvarchar(255)') line1_adr,
    xData.value('line2_adr[1]','nvarchar(255)') line2_adr,
    xData.value('line3_adr[1]','nvarchar(255)') line3_adr,
    xData.value('city_nam[1]','nvarchar(255)') city_name,
    xData.value('st_nam[1]','nvarchar(255)') st_name,
    xData.value('zip_cde[1]','nvarchar(255)') zip_cde,
    xData.value('ctry_nam[1]','nvarchar(255)') ctry_name,
    xData.value('phone_num[1]','decimal(28,10)') phone_num,
    xData.value('fax_num[1]','decimal(28,10)') fax_num,
    xData.value('stat_eff_dte[1]','datetime') stat_eff_dte,
    xData.value('issng_ag_cde[1]','nvarchar(255)') issng_ag_cde,
    xData.value('lst_modified_dte[1]','datetime') last_modified_dte,
    xData.value('created_dte[1]','datetime') created_dte,
    xData.value('ogrid_stat_cde[1]','nvarchar(255)') ogrid_stat_cde
FROM 
    @fileData.nodes('root/ogrid') AS x(xData)

Here's an example of the file with the xsd included when it is received from the source.

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" 
         xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" 
         xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
         xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
         elementFormDefault="qualified">
        <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
             schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
        <xsd:element name="ogrid">
            <xsd:complexType>
                <xsd:sequence>
                    <xsd:element name="ogrid_cde" type="sqltypes:int" nillable="1"/>
                    <xsd:element name="ogrid_nam" nillable="1">
                        <xsd:simpleType>
                            <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

Upvotes: 0

Views: 335

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13440

You must declare the namespace using WITH XMLNAMESPACES and then prefix the names with the schema prefix (ns1 in my example) when query them. So your code should become something like this:

DECLARE @fileData XML

SELECT @fileData = BulkColumn 
FROM OpenRowSet(BULK 'E:\inbox\ogrid.xml',Single_blob) x;

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:sql:SqlRowSet1' as ns1)  
SELECT 
    xdata.value('ns1:ogrid_cde[1]','int') ogrid_cde,
    xData.value('ns1:ogrid_nam[1]','nvarchar(255)') ogrid_name,
    xData.value('ns1:ogrid_adr_nam[1]','nvarchar(255)') ogrid_adr_name,
    xData.value('ns1:mail_stop[1]','nvarchar(255)') mail_stop,
    xData.value('ns1:line1_adr[1]','nvarchar(255)') line1_adr,
    xData.value('ns1:line2_adr[1]','nvarchar(255)') line2_adr,
    xData.value('ns1:line3_adr[1]','nvarchar(255)') line3_adr,
    xData.value('ns1:city_nam[1]','nvarchar(255)') city_name,
    xData.value('ns1:st_nam[1]','nvarchar(255)') st_name,
    xData.value('ns1:zip_cde[1]','nvarchar(255)') zip_cde,
    xData.value('ns1:ctry_nam[1]','nvarchar(255)') ctry_name,
    xData.value('ns1:phone_num[1]','decimal(28,10)') phone_num,
    xData.value('ns1:fax_num[1]','decimal(28,10)') fax_num,
    xData.value('ns1:stat_eff_dte[1]','datetime') stat_eff_dte,
    xData.value('ns1:issng_ag_cde[1]','nvarchar(255)') issng_ag_cde,
    xData.value('ns1:lst_modified_dte[1]','datetime') last_modified_dte,
    xData.value('ns1:created_dte[1]','datetime') created_dte,
    xData.value('ns1:ogrid_stat_cde[1]','nvarchar(255)') ogrid_stat_cde
FROM 
    @fileData.nodes('root/ns1:ogrid') AS x(xData)

Also, decimal(28,10) type doesn't make much sense for phone and fax numbers.

And more important, tell your supervisor that you published all the data - names, addresses, phone numbers, etc. Your company may need to know about that. Next time when you want to do this, modify the XML to leave only few rows in it and anonymize the data.

Upvotes: 0

Related Questions