SQLXML Bulk loading an attribute into a single table

Question

TLDR;

I am attempting to use SQLXML Bulk Loader (4.0) to load XML that looks something like this;

<?xml version = "1.0" encoding = "UTF-8"?>
<CarSales>
 <Client>
  <ID >3</ID>
  <ClientName>John Smith3</ClientName>
  <Country name="Colombia"/>
 </Client>
 <Client>
  <ID>7</ID>
  <ClientName>Slow Sid</ClientName>
  <Country name="Bolivia"/>
 </Client>
 <Client>
  <ID>10</ID>
  <ClientName>Missing Town</ClientName>
  <Country name="Argentina"/>
 </Client>
</CarSales>

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?

---

More Details:

Here is what I have so far for the corresponding XSD (with "Country" commented out)

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql = "urn:schemas-microsoft-com:mapping-schema">
 <xsd:element name = "CarSales" sql:is-constant = "1" >
  <xsd:complexType>
   <xsd:sequence>
    <xsd:element name = "Client" sql:relation="Client_XMLBulkLoad"
          maxOccurs = "unbounded">
      <xsd:complexType>
       <xsd:sequence>
        <xsd:element name = "ID" type = "xsd:integer" 
                     sql:field = "ID" />
        <xsd:element name = "ClientName" type = "xsd:string"
                     sql:field = "ClientName" />
        <!-- <xsd:element name="Country" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:attribute name="name" type="xsd:string" sql:field = "Country" />
                </xsd:complexType> 
        </xsd:element> -->
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
    </xsd:sequence>
   </xsd:complexType>
  </xsd:element>
</xsd:schema>

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.

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".

So how do I capture country name, when it belongs in the same table as client names and ids?

---

Here is the actual schema I am using (it is a nested schema)

Schema root:

<?xml version="1.0" encoding="utf-8"?>
<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">
  <xs:import namespace="http://scap.nist.gov/schema/vulnerability/0.4" schemaLocation="DELTE_app1.xsd" />
  <xs:element name="nvd" sql:is-constant = "1">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="entry" minOccurs="0" maxOccurs="unbounded" sql:relation="NVD.CVE">
          <xs:complexType>
            <xs:sequence>
              <xs:element ref="app1:cwe" minOccurs="0"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Schema child:

<?xml version="1.0" standalone="yes"?>
<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">
  <xs:element name="cwe" msdata:Prefix="vuln" sql:field="CWE">
    <xs:complexType>
        <xs:simpleContent>
            <xs:extension base="xs:string">
                <xs:attribute name="id" form="unqualified" type="xs:string" sql:field="CWE"/>
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>
  </xs:element>
</xs:schema>

XML file I am loading:

<?xml version='1.0' encoding='UTF-8'?>
<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">
<entry id="CVE-2015-0016">
    <vuln:cwe id="CWE-22"/>
  </entry>
</nvd>

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".

Fir

Answer 1

You need a complex type definition with a simple content by addressing the sql field.
Have a look at <xsd:simpleContent> Element and <xsd:extension> Element (complexContent).
I have not tested but following schema should work.
Update: I've tested just now, it works as expected.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="CarSales" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Client" sql:relation="Client_XMLBulkLoad" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="ID" type="xsd:integer" sql:field="ID"/>
              <xsd:element name="ClientName" type="xsd:string" sql:field="ClientName"/>
              <xsd:element name="Country" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:simpleContent>
                    <xsd:extension base="xsd:string">
                      <xsd:attribute name="name" type="xsd:string" sql:field="Country"/>
                    </xsd:extension>
                  </xsd:simpleContent>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Gley

This worked though one follow-up question. The XML in the question is a simplified example of what I am working with. In my actual case, the "Country" element actually mapped to a different column name (column is named "Citizenship"). I added "sql:field="Citizenship"" to the attribute but I was getting an error that said "Country" is not a known column. So I added "sql:field="Citizenship" to the "Country" element, in addition to the attribute in the schema. Did I do the right thing? It appeared to work after that, though now both the attribute and the element have "sql:field="Citizenship" – Fir

@Fir I need to see xml and schema files. – Gley

please look at my updated question which includes the XML in question. Note that I had to add "sql:field="CWE"" to both the element and attribute in question, even though all I care about is the attribute. Is there someway to fix this or can you tell me what is going on? – Fir

@Fir oh now I see. it's mandatory to map sql fields with elements (and attributes if available) unless the element name is not equal to the sql field's. this example works without sql:field because both xsd element and sql field are sharing the same name. it's totally my laziness what make you confused. sorry, I was supposed to specify. – Gley