Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:
<xsd
chema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="CustomColumns">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsdequence>
<xsd:element name="CN_CUST_KEY" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="ITM_SUF_NO" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Model" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Serial" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleYear" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleDate" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd
ateTime" />
</xsdimpleType>
</xsd:element>
<xsd:element name="prd_itm_no" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
</xsdequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsdchema>
This is the SQL query I'm running against the source table:
INSERT INTO [clmnt]
(
name1,
addr1,
addr2,
city,
[state],
zip5,
zip4,
CustomColumns)
SELECT
LastName,
StreetAddress1,
StreetAddress2,
City,
[State],
Postal5,
Postal4,
(SELECT CN_CUST_KEY,
ITM_SUF_NO,
Model,
Serial,
SaleYear,
SaleDate,
prd_itm_no
FROM TblClass_Customers FOR XML PATH (''), ELEMENTS, ROOT('CustomColumns'), TYPE)
FROM TblClass_Customers;
...and here is the error I'm getting:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...
Kind of at a loss here...
This is what the contents of the target xml column should look like:
<CustomColumns>
<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>
<ITM_SUF_NO>45</ITM_SUF_NO>
<Model>alpha</Model>
<Serial>djdjxh3455skx</Serial>
<SaleYear>2005</SaleYear>
<SaleDate>10/25</SaleDate>
<prd_itm_no>df345f</prd_itm_no>
</CustomColumns>
One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...
<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17
It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...
|||You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:
.....
FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers T2;
|||Thanks a million!!!
Do you think you could just real quick explain to me what it is that made your chnges do the trick?
I'm learning from this whole XML experience...
Thanks again!!
|||Phe:
The SELECT alone works fine, but when coupled with the INSERT it blows up:
Code Snippet
INSERT INTO [clmnt]
(
name1,
addr1,
addr2,
city,
[state],
zip5,
zip4,
CustomColumns
)
SELECT
LastName,
StreetAddress1,
StreetAddress2,
City,
[State],
Postal5,
Postal4,
(SELECT CN_CUST_KEY,
ITM_SUF_NO,
Model,
Serial,
SaleYear,
SaleDate,
prd_itm_no
FROM TblClass_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers2 T2
The validation error again:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
I figured out hat the issue is, but not sure how to address it...
If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.
How would you tell the query to select the column/element even if there is no value or NULL in the source column?
I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...
|||In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.
If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.
|||
Phe:
Thanks for the reply....
I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:
Code Snippet
<CustomColumns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<CN_CUST_KEY>1000422</CN_CUST_KEY>
<ITM_SUF_NO>9</ITM_SUF_NO>
<Model>11073954200</Model>
<Serial xsi:nil="true" />
<SaleYear>2003</SaleYear>
<SaleDate>2003-08-23 00:00:00</SaleDate>
<prd_itm_no>73954</prd_itm_no>
</CustomColumns>
Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...
I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?
Am I making sense...? I hope so!!
Thanks again...
|||You can check e.g.
Code Snippet
SELECT @.x.query(
'CustomColumns[Serial/@.xsi:nil = true()]'
);
|||Thanks Martin:
But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element
Last question I swear....!
So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?
For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:
Code Snippet
SELECT clmnt.query('
declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";
/CustomColumns/Serial
') as Result
FROM [CustomColumns].[Serial]
WHERE Serial = '140325'
I'm sure it's completely way off the mark, but if someone could give some insight that would be great.
Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...
Thanks for tolerating an XML noob...
