title: SQLXML Bulk loading an attribute into a single table
body: <p><strong>TLDR;</strong></p>
  
  <p>I am attempting to use SQLXML Bulk Loader (4.0) to load XML that looks something like this;</p>
  
  <pre><code>&lt;?xml version = "1.0" encoding = "UTF-8"?&gt;
  &lt;CarSales&gt;
   &lt;Client&gt;
    &lt;ID &gt;3&lt;/ID&gt;
    &lt;ClientName&gt;John Smith3&lt;/ClientName&gt;
    &lt;Country name="Colombia"/&gt;
   &lt;/Client&gt;
   &lt;Client&gt;
    &lt;ID&gt;7&lt;/ID&gt;
    &lt;ClientName&gt;Slow Sid&lt;/ClientName&gt;
    &lt;Country name="Bolivia"/&gt;
   &lt;/Client&gt;
   &lt;Client&gt;
    &lt;ID&gt;10&lt;/ID&gt;
    &lt;ClientName&gt;Missing Town&lt;/ClientName&gt;
    &lt;Country name="Argentina"/&gt;
   &lt;/Client&gt;
  &lt;/CarSales&gt;
  </code></pre>
  
  <p><strong>I want the Country name attribute to be captured in a column of the only table I am using ("Client_XMLBulkLoad", which also holds client name, and ID). Is that possible?</strong></p>
  
  <hr>
  
  <p><strong>More Details</strong>:</p>
  
  <p>Here is what I have so far for the corresponding XSD (with "Country" commented out)</p>
  
  <pre><code>&lt;xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql = "urn:schemas-microsoft-com:mapping-schema"&gt;
   &lt;xsd:element name = "CarSales" sql:is-constant = "1" &gt;
    &lt;xsd:complexType&gt;
     &lt;xsd:sequence&gt;
      &lt;xsd:element name = "Client" sql:relation="Client_XMLBulkLoad"
            maxOccurs = "unbounded"&gt;
        &lt;xsd:complexType&gt;
         &lt;xsd:sequence&gt;
          &lt;xsd:element name = "ID" type = "xsd:integer" 
                       sql:field = "ID" /&gt;
          &lt;xsd:element name = "ClientName" type = "xsd:string"
                       sql:field = "ClientName" /&gt;
          &lt;!-- &lt;xsd:element name="Country" minOccurs="0" maxOccurs="unbounded"&gt;
                  &lt;xsd:complexType&gt;
                    &lt;xsd:attribute name="name" type="xsd:string" sql:field = "Country" /&gt;
                  &lt;/xsd:complexType&gt; 
          &lt;/xsd:element&gt; --&gt;
         &lt;/xsd:sequence&gt;
        &lt;/xsd:complexType&gt;
       &lt;/xsd:element&gt;
      &lt;/xsd:sequence&gt;
     &lt;/xsd:complexType&gt;
    &lt;/xsd:element&gt;
  &lt;/xsd:schema&gt;
  </code></pre>
  
  <p>If I attempt a bulk load, with Country as-is, I get an error stating that a schema relationship is expected on 'Country'. However, in everything I am reading, relationships are setup between different tables, not for importing attributes on the same table as all other data. </p>
  
  <p>Alternatively I have tried marking the "Country" element with an "is-constant="1"" flag, but that results in an error that states that "constant/fixed element cannot have attributes".</p>
  
  <p>So how do I capture country name, when it belongs in the same table as client names and ids?</p>
  
  <hr>
  
  <p>Here is the actual schema I am using (it is a nested schema)</p>
  
  <p>Schema root:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;xs:schema id="NewDataSet" targetNamespace="http://scap.nist.gov/schema/feed/vulnerability/2.0" xmlns:mstns="http://scap.nist.gov/schema/feed/vulnerability/2.0" xmlns="http://scap.nist.gov/schema/feed/vulnerability/2.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:app1="http://scap.nist.gov/schema/vulnerability/0.4" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"&gt;
    &lt;xs:import namespace="http://scap.nist.gov/schema/vulnerability/0.4" schemaLocation="DELTE_app1.xsd" /&gt;
    &lt;xs:element name="nvd" sql:is-constant = "1"&gt;
      &lt;xs:complexType&gt;
        &lt;xs:sequence&gt;
          &lt;xs:element name="entry" minOccurs="0" maxOccurs="unbounded" sql:relation="NVD.CVE"&gt;
            &lt;xs:complexType&gt;
              &lt;xs:sequence&gt;
                &lt;xs:element ref="app1:cwe" minOccurs="0"/&gt;
              &lt;/xs:sequence&gt;
            &lt;/xs:complexType&gt;
          &lt;/xs:element&gt;
        &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
    &lt;/xs:element&gt;
  &lt;/xs:schema&gt;
  </code></pre>
  
  <p>Schema child:</p>
  
  <pre><code>&lt;?xml version="1.0" standalone="yes"?&gt;
  &lt;xs:schema targetNamespace="http://scap.nist.gov/schema/vulnerability/0.4" xmlns:mstns="http://scap.nist.gov/schema/feed/vulnerability/2.0" xmlns="http://scap.nist.gov/schema/vulnerability/0.4" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:app2="http://cpe.mitre.org/language/2.0" xmlns:app3="http://scap.nist.gov/schema/cvss-v2/0.2" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"&gt;
    &lt;xs:element name="cwe" msdata:Prefix="vuln" sql:field="CWE"&gt;
      &lt;xs:complexType&gt;
          &lt;xs:simpleContent&gt;
              &lt;xs:extension base="xs:string"&gt;
                  &lt;xs:attribute name="id" form="unqualified" type="xs:string" sql:field="CWE"/&gt;
              &lt;/xs:extension&gt;
          &lt;/xs:simpleContent&gt;
      &lt;/xs:complexType&gt;
    &lt;/xs:element&gt;
  &lt;/xs:schema&gt;
  </code></pre>
  
  <p>XML file I am loading:</p>
  
  <pre><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt;
  &lt;nvd xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.1" xmlns:cvss="http://scap.nist.gov/schema/cvss-v2/0.2" xmlns:vuln="http://scap.nist.gov/schema/vulnerability/0.4" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:patch="http://scap.nist.gov/schema/patch/0.1" xmlns="http://scap.nist.gov/schema/feed/vulnerability/2.0" xmlns:cpe-lang="http://cpe.mitre.org/language/2.0" nvd_xml_version="2.0" pub_date="2015-07-10T03:00:00" xsi:schemaLocation="http://scap.nist.gov/schema/patch/0.1 http://nvd.nist.gov/schema/patch_0.1.xsd http://scap.nist.gov/schema/feed/vulnerability/2.0 http://nvd.nist.gov/schema/nvd-cve-feed_2.0.xsd http://scap.nist.gov/schema/scap-core/0.1 http://nvd.nist.gov/schema/scap-core_0.1.xsd"&gt;
  &lt;entry id="CVE-2015-0016"&gt;
      &lt;vuln:cwe id="CWE-22"/&gt;
    &lt;/entry&gt;
  &lt;/nvd&gt;
  </code></pre>
  
  <p>Note this works BUT I need to have [sql:field="CWE"] at both the element level and at the attribute level. If I dont put that extra "sql:field" I get an error saying "the column 'cwe' was defined in the schema but does not exist in the database". If I add an 'is constant' tag on that element then I get the constant element cannot contain attribute error. So the only solution I have found is to redundantly assign it the sql:field="CWE".</p>