sql - 将 JSON 转换为 SQL 行
问题描述
我在 JSON 下面有这个，我想将它们加载到我的 MSSQL 表中
[{"Item_name": ["Lizinka", "L4690.S53", "Alder Pebble Grain Leather Boot w/ Walking Sole", "Becketts Oxford", "Phillip II Oxford", "Helston Moc Toe Boot", "Croft Pebble Grain Leather Derby w/ Walking Sole", "Croft Pebble Grain Leather Derby w/ Walking Sole", "City II Cap Toe Oxford", "Loe Museum Calf Derby w/ Classic Rubber Sole", "Trent Museum Calf Oxford w/ Single Leather Sole", "Lopez Loafer", "Lopez Loafer", "Lopez Loafer", "E000030", "E000037", "L1409.74", "L1454.74", "E000051", "I900068", "EU00048", "E070005", "Arizona Boot", "IU90054", "PS29238", "I980008", "Suede Over the Knee Boot", "E050000", "I900005", "A87280-MFN796", "RU90068", "EU00042", "E970021A", "E000027", "IU90012", "Wren Cap Toe Oxford", "Adley Loafer", "L4723", "Olympus Mons EVO", "IU90028", "Harmony Sparkle Rhinestone Chain Strap Heel Sandal", "EU00023", "May London High Top Embroidered Sneaker", "E000015", "E050006", "I900029", "RU90007", "Ciconia 70", "Tolk", "RU90059", "Cassapara Reversed Leather Gum Sole Chelsea", "PS33085A", "Joan", "Joan", "Holme Monk Sneaker", "Lewis Slant Zip Boot", "I970023", "Kenneth Cap Toe Oxford", "PS31108", "Fox Loafer", "Lesley 75", "Lesley 75", "RS00020", "EU00030", "RS00031", "EU00043", "E000053", "RM00018", "Soraya", "Pull-On Boot", "PS34045A", "Tyne Loafer", "RU90026A", "Burwood Oxford", "Brooklyn Chi Sneaker", "Susanne", "Benta", "Kay", "Benta", "G2 SM", "EU00017", "RM00031", "E050001", "RU90026", "Liberty & Justice", "Blowout", "Selena Crystal", "Selena Crystal", "Harmony Heel Sandal", "I900037", "RU90055", "Kali", "Evett Sneaker", "Danzey Boot", "Danzey Boot", "Dolly Platform Pump", "D-Bumpy One Sneaker", "Fiandra Intrecciato Loafer", "I950005", "Lugosi"], "Item_price": ["$4,330.00", "$2,339.96", "$2,000.00", "$1,910.00", "$1,910.00", "$1,630.00", "$1,615.00", "$1,615.00", "$1,490.00", "$1,490.00", "$1,490.00", "$1,450.00", "$1,450.00", "$1,450.00", "$1,450.00", "$1,450.00", "$1,435.96", "$1,435.96", "$1,350.00", "$1,299.97", "$1,295.00", "$1,295.00", "$1,240.00", "$1,195.00", "$1,185.99", "$1,165.50", "$1,161.00", "$1,150.00", "$1,125.00", "$1,100.00", "$1,095.00", "$1,095.00", "$1,095.00", "$1,095.00", "$1,095.00", "$1,040.00", "$1,040.00", "$1,035.96", "$999.00", "$995.00", "$995.00", "$995.00", "$995.00", "$995.00", "$995.00", "$995.00", "$995.00", "$995.00", "$975.00", "$950.00", "$948.00", "$935.00", "$930.00", "$930.00", "$920.00", "$898.00", "$897.00", "$895.99", "$895.99", "$895.00", "$895.00", "$895.00", "$895.00", "$895.00", "$895.00", "$895.00", "$895.00", "$895.00", "$885.00", "$880.00", "$875.00", "$865.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$850.00", "$849.95", "$849.95", "$845.00", "$845.00", "$845.00", "$845.00", "$825.00", "$825.00", "$820.00", "$820.00", "$820.00", "$815.00", "$810.00", "$810.00", "$805.00", "$800.00"], "Item_brandname": ["Salvatore Ferragamo", "Lucchese", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "John Lobb", "Giuseppe Zanotti", "Giuseppe Zanotti", "Lucchese", "Lucchese", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "DSQUARED2", "Giuseppe Zanotti", "Proenza Schouler", "Giuseppe Zanotti", "MARNI", "Giuseppe Zanotti", "Giuseppe Zanotti", "Sergio Rossi", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "John Lobb", "John Lobb", "Lucchese", "La Sportiva", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Salvatore Ferragamo", "Salvatore Ferragamo", "Giuseppe Zanotti", "Marsell", "Proenza Schouler", "Salvatore Ferragamo", "Salvatore Ferragamo", "John Lobb", "John Varvatos Collection", "Giuseppe Zanotti", "Santoni", "Proenza Schouler", "Santoni", "Stuart Weitzman", "Stuart Weitzman", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Rupert Sanderson", "Gravati", "Proenza Schouler", "John Lobb", "Giuseppe Zanotti", "Church's", "Salvatore Ferragamo", "Salvatore Ferragamo", "Alexandre Birman", "Salvatore Ferragamo", "Alexandre Birman", "La Sportiva", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Double D Ranchwear by Old Gringo", "Double D Ranchwear by Old Gringo", "Tabitha Simmons", "Tabitha Simmons", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Tabitha Simmons", "John Lobb", "Church's", "Church's", "Charlotte Olympia", "DSQUARED2", "Bottega Veneta", "Giuseppe Zanotti", "Jeffery-West"]},
{"Item_name": ["Leather Ankle Boot", "Ammunition", "Tieland Over the Knee Boot", "Lowland Over the Knee Boot", "Lowland Over the Knee Boot", "Lowland Over the Knee Boot", "Tieland Over the Knee Boot", "Lewis Buckle Chelsea Boot", "RU70009N", "Betty Platform Sandal", "Kali Bis", "RW90066", "RW90055", "Kali Bis", "Leni Crystal", "Patton", "Kali Bis", "Xaren", "Xamira", "Cimbra Sneaker", "Monica", "Lesley 75", "Urchin Sneaker", "Urchin Sneaker", "Betty Platform Sandal", "Monica", "Osimo Pump", "RW70009A", "Lida SW", "Aicha", "Aicha", "Rachel", "Sherman Loafer", "Stanford Oxford", "Chiara Embroidery Sandal", "EU00038", "RS00019", "RM00042", "I700055A", "E000024", "I900056", "E000034", "Rachel Bootie", "Vicky Boot 50", "IU90035", "Ketsby Boot", "Monmouth Boot", "Consul 173 Oxford", "RW90069", "Celina 2", "SR1", "PS33095A", "Round Up Rosie", "Round Up Rosie", "Savanna", "Knit Paisley Sneaker", "Ravello Block Heel Sandal", "Meadow Printed Croc", "Cross Strap Boot", "Mountain Lace-Up Boot", "Consul Oxford", "Gardena", "Soft Sneaker", "SR1 Sandal", "Double Sketch Low Top Sneaker", "Asten Loafer", "A87300-MFN802", "Sidney Loafer", "RS00044", "E000022A", "E000049", "E000032", "Spantik", "Mercantile Makeshifter", "Almost Famous", "Hermione", "Tippie Pump", "Levah Sneaker", "Levah Sneaker", "Deirde", "Levah Sneaker", "Ortensia 55", "Belshaw Patent Oxford", "Haven Suede Slip-On Sneaker", "Swing", "Jessa 95 mm Rectangle Hard", "Kyoto", "Wayne", "Amatea 70", "Emilia Sling Back", "RW90017", "Dodger II Slip-On Sneaker", "May London Mid Top Zayn Sneaker", "I970049", "L4625", "90 mm Boot", "Eldridge Harness Boot", "Morrison Sharpei Boot", "Morrison Sharpei Boot", "Fleetwood Lace Boot"], "Item_price": ["$800.00", "$799.95", "$798.00", "$798.00", "$798.00", "$798.00", "$798.00", "$798.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$795.00", "$790.00", "$790.00", "$790.00", "$787.50", "$775.00", "$775.00", "$775.00", "$774.95", "$774.95", "$765.00", "$760.00", "$760.00", "$760.00", "$755.99", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$750.00", "$749.95", "$749.95", "$745.00", "$740.00", "$735.00", "$735.00", "$735.00", "$735.00", "$730.00", "$730.00", "$730.00", "$729.27", "$728.00", "$725.00", "$715.99", "$715.30", "$715.00", "$711.75", "$710.00", "$710.00", "$700.00", "$699.96", "$698.00", "$698.00", "$698.00", "$698.00", "$698.00"], "Item_brandname": ["Gravati", "Double D Ranchwear by Old Gringo", "Stuart Weitzman", "Stuart Weitzman", "Stuart Weitzman", "Stuart Weitzman", "Stuart Weitzman", "John Varvatos Collection", "Giuseppe Zanotti", "Giuseppe Zanotti", "Tabitha Simmons", "Giuseppe Zanotti", "Giuseppe Zanotti", "Tabitha Simmons", "Tabitha Simmons", "Tabitha Simmons", "Tabitha Simmons", "Pedro Garcia", "Pedro Garcia", "Salvatore Ferragamo", "Sergio Rossi", "Stuart Weitzman", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Sergio Rossi", "Salvatore Ferragamo", "Giuseppe Zanotti", "Salvatore Ferragamo", "Salvatore Ferragamo", "Salvatore Ferragamo", "Clergerie", "Salvatore Ferragamo", "Salvatore Ferragamo", "Sophia Webster", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Alexandre Birman", "Alexandre Birman", "Giuseppe Zanotti", "Church's", "Church's", "Church's", "Giuseppe Zanotti", "Clergerie", "Sergio Rossi", "Proenza Schouler", "Double D Ranchwear by Old Gringo", "Double D Ranchwear by Old Gringo", "Rupert Sanderson", "Etro", "Bottega Veneta", "Rupert Sanderson", "Gravati", "Santoni", "Church's", "Salvatore Ferragamo", "Salvatore Ferragamo", "Sergio Rossi", "Giuseppe Zanotti", "Salvatore Ferragamo", "Sergio Rossi", "Salvatore Ferragamo", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "Giuseppe Zanotti", "La Sportiva", "Double D Ranchwear by Old Gringo", "Double D Ranchwear by Old Gringo", "Tabitha Simmons", "Bottega Veneta", "John Lobb", "John Lobb", "Rupert Sanderson", "John Lobb", "Salvatore Ferragamo", "Salvatore Ferragamo", "John Lobb", "Tabitha Simmons", "Tory Burch", "Rupert Sanderson", "Clergerie", "Salvatore Ferragamo", "Charlotte Olympia", "Giuseppe Zanotti", "Bottega Veneta", "Giuseppe Zanotti", "Giuseppe Zanotti", "Lucchese", "Tory Burch", "John Varvatos Collection", "John Varvatos Collection", "John Varvatos Collection", "John Varvatos Collection"]}]
下面是我正在寻找的输出。
"Item_name","Item_price","Item_brandname"
"Lizinka","$4,330.00","Salvatore Ferragamo"
"L4690.S53","$2,339.96","Lucchese"
"Alder Pebble Grain Leather Boot w/ Walking Sole","$2,000.00","John Lobb"
"Becketts Oxford","$1,910.00","John Lobb"
"Phillip II Oxford","$1,910.00","John Lobb"
"Helston Moc Toe Boot","$1,630.00","John Lobb"
"Croft Pebble Grain Leather Derby w/ Walking Sole","$1,615.00","John Lobb"
"Croft Pebble Grain Leather Derby w/ Walking Sole","$1,615.00","John Lobb"
"City II Cap Toe Oxford","$1,490.00","John Lobb"
"Loe Museum Calf Derby w/ Classic Rubber Sole","$1,490.00","John Lobb"
"Trent Museum Calf Oxford w/ Single Leather Sole","$1,490.00","John Lobb"
"Lopez Loafer","$1,450.00","John Lobb"
"Lopez Loafer","$1,450.00","John Lobb"
"Lopez Loafer","$1,450.00","John Lobb"
"E000030","$1,450.00","Giuseppe Zanotti"
"E000037","$1,450.00","Giuseppe Zanotti"
"L1409.74","$1,435.96","Lucchese"
"L1454.74","$1,435.96","Lucchese"
"E000051","$1,350.00","Giuseppe Zanotti"
"I900068","$1,299.97","Giuseppe Zanotti"
"EU00048","$1,295.00","Giuseppe Zanotti"
"E070005","$1,295.00","Giuseppe Zanotti"
"Arizona Boot","$1,240.00","DSQUARED2"
"IU90054","$1,195.00","Giuseppe Zanotti"
"PS29238","$1,185.99","Proenza Schouler"
"I980008","$1,165.50","Giuseppe Zanotti"
"Suede Over the Knee Boot","$1,161.00","MARNI"
"E050000","$1,150.00","Giuseppe Zanotti"
"I900005","$1,125.00","Giuseppe Zanotti"
"A87280-MFN796","$1,100.00","Sergio Rossi"
"RU90068","$1,095.00","Giuseppe Zanotti"
"EU00042","$1,095.00","Giuseppe Zanotti"
"E970021A","$1,095.00","Giuseppe Zanotti"
"E000027","$1,095.00","Giuseppe Zanotti"
"IU90012","$1,095.00","Giuseppe Zanotti"
"Wren Cap Toe Oxford","$1,040.00","John Lobb"
"Adley Loafer","$1,040.00","John Lobb"
"L4723","$1,035.96","Lucchese"
"Olympus Mons EVO","$999.00","La Sportiva"
"IU90028","$995.00","Giuseppe Zanotti"
"Harmony Sparkle Rhinestone Chain Strap Heel Sandal","$995.00","Giuseppe Zanotti"
"EU00023","$995.00","Giuseppe Zanotti"
"May London High Top Embroidered Sneaker","$995.00","Giuseppe Zanotti"
"E000015","$995.00","Giuseppe Zanotti"
"E050006","$995.00","Giuseppe Zanotti"
"I900029","$995.00","Giuseppe Zanotti"
"RU90007","$995.00","Giuseppe Zanotti"
"Ciconia 70","$995.00","Salvatore Ferragamo"
"Tolk","$975.00","Salvatore Ferragamo"
"RU90059","$950.00","Giuseppe Zanotti"
"Cassapara Reversed Leather Gum Sole Chelsea","$948.00","Marsell"
"PS33085A","$935.00","Proenza Schouler"
"Joan","$930.00","Salvatore Ferragamo"
"Joan","$930.00","Salvatore Ferragamo"
"Holme Monk Sneaker","$920.00","John Lobb"
"Lewis Slant Zip Boot","$898.00","John Varvatos Collection"
"I970023","$897.00","Giuseppe Zanotti"
"Kenneth Cap Toe Oxford","$895.99","Santoni"
"PS31108","$895.99","Proenza Schouler"
"Fox Loafer","$895.00","Santoni"
"Lesley 75","$895.00","Stuart Weitzman"
"Lesley 75","$895.00","Stuart Weitzman"
"RS00020","$895.00","Giuseppe Zanotti"
"EU00030","$895.00","Giuseppe Zanotti"
"RS00031","$895.00","Giuseppe Zanotti"
"EU00043","$895.00","Giuseppe Zanotti"
"E000053","$895.00","Giuseppe Zanotti"
"RM00018","$895.00","Giuseppe Zanotti"
"Soraya","$885.00","Rupert Sanderson"
"Pull-On Boot","$880.00","Gravati"
"PS34045A","$875.00","Proenza Schouler"
"Tyne Loafer","$865.00","John Lobb"
"RU90026A","$850.00","Giuseppe Zanotti"
"Burwood Oxford","$850.00","Church's"
"Brooklyn Chi Sneaker","$850.00","Salvatore Ferragamo"
"Susanne","$850.00","Salvatore Ferragamo"
"Benta","$850.00","Alexandre Birman"
"Kay","$850.00","Salvatore Ferragamo"
"Benta","$850.00","Alexandre Birman"
"G2 SM","$850.00","La Sportiva"
"EU00017","$850.00","Giuseppe Zanotti"
"RM00031","$850.00","Giuseppe Zanotti"
"E050001","$850.00","Giuseppe Zanotti"
"RU90026","$850.00","Giuseppe Zanotti"
"Liberty & Justice","$849.95","Double D Ranchwear by Old Gringo"
"Blowout","$849.95","Double D Ranchwear by Old Gringo"
"Selena Crystal","$845.00","Tabitha Simmons"
"Selena Crystal","$845.00","Tabitha Simmons"
"Harmony Heel Sandal","$845.00","Giuseppe Zanotti"
"I900037","$845.00","Giuseppe Zanotti"
"RU90055","$825.00","Giuseppe Zanotti"
"Kali","$825.00","Tabitha Simmons"
"Evett Sneaker","$820.00","John Lobb"
"Danzey Boot","$820.00","Church's"
"Danzey Boot","$820.00","Church's"
"Dolly Platform Pump","$815.00","Charlotte Olympia"
"D-Bumpy One Sneaker","$810.00","DSQUARED2"
"Fiandra Intrecciato Loafer","$810.00","Bottega Veneta"
"I950005","$805.00","Giuseppe Zanotti"
"Lugosi","$800.00","Jeffery-West"
"Leather Ankle Boot","$800.00","Gravati"
"Ammunition","$799.95","Double D Ranchwear by Old Gringo"
"Tieland Over the Knee Boot","$798.00","Stuart Weitzman"
"Lowland Over the Knee Boot","$798.00","Stuart Weitzman"
"Lowland Over the Knee Boot","$798.00","Stuart Weitzman"
"Lowland Over the Knee Boot","$798.00","Stuart Weitzman"
"Tieland Over the Knee Boot","$798.00","Stuart Weitzman"
"Lewis Buckle Chelsea Boot","$798.00","John Varvatos Collection"
"RU70009N","$795.00","Giuseppe Zanotti"
"Betty Platform Sandal","$795.00","Giuseppe Zanotti"
"Kali Bis","$795.00","Tabitha Simmons"
"RW90066","$795.00","Giuseppe Zanotti"
"RW90055","$795.00","Giuseppe Zanotti"
"Kali Bis","$795.00","Tabitha Simmons"
"Leni Crystal","$795.00","Tabitha Simmons"
"Patton","$795.00","Tabitha Simmons"
"Kali Bis","$795.00","Tabitha Simmons"
"Xaren","$795.00","Pedro Garcia"
"Xamira","$795.00","Pedro Garcia"
"Cimbra Sneaker","$795.00","Salvatore Ferragamo"
"Monica","$795.00","Sergio Rossi"
"Lesley 75","$795.00","Stuart Weitzman"
"Urchin Sneaker","$795.00","Giuseppe Zanotti"
"Urchin Sneaker","$795.00","Giuseppe Zanotti"
"Betty Platform Sandal","$795.00","Giuseppe Zanotti"
"Monica","$795.00","Sergio Rossi"
"Osimo Pump","$795.00","Salvatore Ferragamo"
"RW70009A","$795.00","Giuseppe Zanotti"
"Lida SW","$795.00","Salvatore Ferragamo"
"Aicha","$795.00","Salvatore Ferragamo"
"Aicha","$795.00","Salvatore Ferragamo"
"Rachel","$795.00","Clergerie"
"Sherman Loafer","$795.00","Salvatore Ferragamo"
"Stanford Oxford","$795.00","Salvatore Ferragamo"
"Chiara Embroidery Sandal","$795.00","Sophia Webster"
"EU00038","$795.00","Giuseppe Zanotti"
"RS00019","$795.00","Giuseppe Zanotti"
"RM00042","$795.00","Giuseppe Zanotti"
"I700055A","$795.00","Giuseppe Zanotti"
"E000024","$795.00","Giuseppe Zanotti"
"I900056","$795.00","Giuseppe Zanotti"
"E000034","$795.00","Giuseppe Zanotti"
"Rachel Bootie","$795.00","Alexandre Birman"
"Vicky Boot 50","$795.00","Alexandre Birman"
"IU90035","$795.00","Giuseppe Zanotti"
"Ketsby Boot","$790.00","Church's"
"Monmouth Boot","$790.00","Church's"
"Consul 173 Oxford","$790.00","Church's"
"RW90069","$787.50","Giuseppe Zanotti"
"Celina 2","$775.00","Clergerie"
"SR1","$775.00","Sergio Rossi"
"PS33095A","$775.00","Proenza Schouler"
"Round Up Rosie","$774.95","Double D Ranchwear by Old Gringo"
"Round Up Rosie","$774.95","Double D Ranchwear by Old Gringo"
"Savanna","$765.00","Rupert Sanderson"
"Knit Paisley Sneaker","$760.00","Etro"
"Ravello Block Heel Sandal","$760.00","Bottega Veneta"
"Meadow Printed Croc","$760.00","Rupert Sanderson"
"Cross Strap Boot","$755.99","Gravati"
"Mountain Lace-Up Boot","$750.00","Santoni"
"Consul Oxford","$750.00","Church's"
"Gardena","$750.00","Salvatore Ferragamo"
"Soft Sneaker","$750.00","Salvatore Ferragamo"
"SR1 Sandal","$750.00","Sergio Rossi"
"Double Sketch Low Top Sneaker","$750.00","Giuseppe Zanotti"
"Asten Loafer","$750.00","Salvatore Ferragamo"
"A87300-MFN802","$750.00","Sergio Rossi"
"Sidney Loafer","$750.00","Salvatore Ferragamo"
"RS00044","$750.00","Giuseppe Zanotti"
"E000022A","$750.00","Giuseppe Zanotti"
"E000049","$750.00","Giuseppe Zanotti"
"E000032","$750.00","Giuseppe Zanotti"
"Spantik","$750.00","La Sportiva"
"Mercantile Makeshifter","$749.95","Double D Ranchwear by Old Gringo"
"Almost Famous","$749.95","Double D Ranchwear by Old Gringo"
"Hermione","$745.00","Tabitha Simmons"
"Tippie Pump","$740.00","Bottega Veneta"
"Levah Sneaker","$735.00","John Lobb"
"Levah Sneaker","$735.00","John Lobb"
"Deirde","$735.00","Rupert Sanderson"
"Levah Sneaker","$735.00","John Lobb"
"Ortensia 55","$730.00","Salvatore Ferragamo"
"Belshaw Patent Oxford","$730.00","Salvatore Ferragamo"
"Haven Suede Slip-On Sneaker","$730.00","John Lobb"
"Swing","$729.27","Tabitha Simmons"
"Jessa 95 mm Rectangle Hard","$728.00","Tory Burch"
"Kyoto","$725.00","Rupert Sanderson"
"Wayne","$715.99","Clergerie"
"Amatea 70","$715.30","Salvatore Ferragamo"
"Emilia Sling Back","$715.00","Charlotte Olympia"
"RW90017","$711.75","Giuseppe Zanotti"
"Dodger II Slip-On Sneaker","$710.00","Bottega Veneta"
"May London Mid Top Zayn Sneaker","$710.00","Giuseppe Zanotti"
"I970049","$700.00","Giuseppe Zanotti"
"L4625","$699.96","Lucchese"
"90 mm Boot","$698.00","Tory Burch"
"Eldridge Harness Boot","$698.00","John Varvatos Collection"
"Morrison Sharpei Boot","$698.00","John Varvatos Collection"
"Morrison Sharpei Boot","$698.00","John Varvatos Collection"
"Fleetwood Lace Boot","$698.00","John Varvatos Collection"
我正在使用此代码推JSON送到SQL
DECLARE @json NVARCHAR(500)
SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK 'C:\abc\data.json', SINGLE_CLOB)
AS j
SELECT Item_Name
,Item_brandname
,Item_saleprice
FROM
OPENJSON ( @JSON )
WITH ( Item_Name nvarchar(50) '$.Item_Name'
,Item_brandname nvarchar(50) '$.Item_brandname'
,Item_saleprice nvarchar(50) '$.Item_saleprice'
)
我收到一条错误消息
Msg 13609, Level 16, State 4, Line 12
JSON text is not properly formatted. Unexpected character '"' is found at position 493.
我尝试使用在线 JSON 格式来检查我的 JSON 是否错误，但我得到相同的错误不确定问题到底出在哪里。
更新了代码以获取JSONasSINGLE_CLOB
解决方案
输入 JSON 包含嵌套的 JSON 数组，因此您需要为每个嵌套数组使用OPENJSON()默认模式和附加运算符。APPLY调用的结果OPENJSON()是一个带有列的表，key如果是 JSON 数组，该列包含该数组中每个项目的索引。valuetypekey
JSON（简化）：
DECLARE @json nvarchar(max) = N'
[
{
"Item_name":[
"Lizinka",
"L4690.S53",
"Alder Pebble Grain Leather Boot w/ Walking Sole"
],
"Item_price":[
"$4,330.00",
"$2,339.96",
"$2,000.00"
],
"Item_brandname":[
"Salvatore Ferragamo",
"Lucchese",
"John Lobb"
]
},
{
"Item_name":[
"Leather Ankle Boot",
"Ammunition",
"Tieland Over the Knee Boot"
],
"Item_price":[
"$800.00",
"$799.95",
"$798.00"
],
"Item_brandname":[
"Gravati",
"Double D Ranchwear by Old Gringo",
"Stuart Weitzman"
]
}
]'
陈述：
SELECT
j1.[value] AS Item_name,
j2.[value] AS Item_price,
j3.[value] AS Item_brandname
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON (j.[value], '$.Item_name') j1
CROSS APPLY OPENJSON (j.[value], '$.Item_price') j2
CROSS APPLY OPENJSON (j.[value], '$.Item_brandname') j3
WHERE j1.[key] = j2.[key] AND j2.[key] = j3.[key]
结果：
--------------------------------------------------------------------------------------------
Item_name Item_price Item_brandname
--------------------------------------------------------------------------------------------
Lizinka $4,330.00 Salvatore Ferragamo
L4690.S53 $2,339.96 Lucchese
Alder Pebble Grain Leather Boot w/ Walking Sole $2,000.00 John Lobb
Leather Ankle Boot $800.00 Gravati
Ammunition $799.95 Double D Ranchwear by Old Gringo
Tieland Over the Knee Boot $798.00 Stuart Weitzman