😀

The logic is exactly the same, I just short-handed the CASE statement with a little math. Note that the math...

Quick solution using the APPLY operator

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @MappingTable TABLE

(

Productname nvarchar(10),

identification_key1 int,

identification_key2 int,

identification_key3 int

);

insert into @MappingTable(Productname,identification_key1,identification_key2,identification_key3)

VALUES ('Apple' , 1,2,1)

,('Orange', 1,2,3)

...

Further on Jeff's answer, could you post the DDL for the table and everything that comes with it (indexes, constraints, triggers etc.)?

😎

Jeff Moden (2/4/2015)

---

Sean Lange (2/4/2015)

---

Sioban Krzywicki (2/4/2015)

---

Sean Lange (2/4/2015)

---

Sioban Krzywicki (2/4/2015)

---

Grant Fritchey (2/4/2015)

---

Eirikur Eiriksson (2/4/2015)

---

Sioban Krzywicki (2/4/2015)

---

Grant Fritchey (2/4/2015)

---

Brandie Tarvin (2/4/2015)

---

Anyone know anything about this Grant guy? Does he...

ma701ss (2/4/2015)

---

I don't understand that. Why do you think it worked for you? If you don't mind, what happens if you import the XML with every field as varchar(50) and...

This should get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<?xml version="1.0"?>

<STOREITEMS>

<CREATED>

<CATEGORY id="127" name="Category 1">

<PRODUCT ITEM="4023">

<NAME>Patriot</NAME>

<MODEL>7505</MODEL>

<PRICE>10.46</PRICE>

<RRP>20.00</RRP>

<THUMB>07505ds.jpg</THUMB>

<IMAGE>07505.jpg</IMAGE>

<MULTI/>

<MULTI1/>

<MULTI2/>

<MULTI3/>

<BIGMULTI1/>

<BIGMULTI2/>

<BIGMULTI3/>

<DESCRIPTION>Description.</DESCRIPTION>

<XIMAGE>07505x.jpg</XIMAGE>

<XIMAGE2>07505x1.jpg</XIMAGE2>

<XIMAGE3>07505x2.jpg</XIMAGE3>

<XIMAGE4/>

<XIMAGE5/>

<LENGTH>9</LENGTH>

<DIAMETER>1.75</DIAMETER>

<ORINGCIRC/>

<ORINGDIAM/>

<WIDTH>5</WIDTH>

<CONTROLLER>Built In</CONTROLLER>

<WHATISIT/>

<FOR/>

<MOTION/>

<FEATURES>Multi speed</FEATURES>

<MISC/>

<STYLE/>

<POWER>2 x AA (Included)</POWER>

<SIZE>7</SIZE>

<ATTRIBUTES NAME="Batteries" ATTRIBUTEID="9">

<ATTRIBUTEVALUES VALUE="190" TITLE="None" PRICEADJUST="+0.00"/>

<ATTRIBUTEVALUES VALUE="182"...

Sioban Krzywicki (2/4/2015)

---

Grant Fritchey (2/4/2015)

---

Nah, I hear he's a giant bag of wind and...

Brandie Tarvin (2/4/2015)

---

... a book called SQL Server 2012 Query Performance Tuning by some guy named Grant Fritchey... Anyone know anything about this Grant guy? Does he know what he's...

Mindy Hreczuck (2/3/2015)

---

I have a schema with 9 fields originally set at char(32) and will be expanded to char(64).

I'm trying to figure out which databases this expansion will take...

ma701ss (2/3/2015)

---

Hi,

I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name...

Quick thought, this code looks very inefficient, while the logic is rather simple, should be straight forward to construct a set based code for the operation. Could you post DDL...

Quick thought, maybe this will help

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<ROOT><code>1</code><code>2</code><code>3</code><code /></ROOT>';

SELECT

ROW_NUMBER() OVER

(

...

sqldriver (2/2/2015)

---

That was one of the first things I...

Grant Fritchey (2/2/2015)

---

Sioban Krzywicki (2/2/2015)

---

SQLRNNR (2/2/2015)

---

I have to...

Quick thought, the problem itself is straightforward, the issue is more the schema, normalize it and the solution is easy.

😎