June 19, 2014 at 2:00 am
Anyone has any suggestions? ^^
I found loads of things but nothing seems to work...
I'm trying to get a link with XML data inside the page into a table but I can't find anything
June 19, 2014 at 5:18 am
You need to post here the Table and XML structure..
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 7:42 am
<ProductsResponse xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Operations">
<Products xmlns:d2p1="http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Types">
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2007-12-27T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx stuurinrichting VR</d2p1:Description>
<d2p1:DescriptionLong>
Tacx Virtual Reality stuurframe , Wordt standaard geleverd bij de Fortius Multiplayer en is als optie leverbaar bij de andere twee VR trainers. Om het dynamische en interactieve spelelement optimaal te ervaren is het VR stuurframe een absolute must. Elke beweging van het stuur is direct zichtbaar op het scherm.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895016436</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/ffc7517a358342a4aaf5d3653f9d5095.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>97.90</d2p1:PriceGross>
<d2p1:ProductCode>HTT1905</d2p1:ProductCode>
<d2p1:ProductGroup>77UVA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometraineraccessoires</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>178.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T1905</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2008-11-19T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx trainer Bushido/skyliner T1980</d2p1:Description>
<d2p1:DescriptionLong>
Tacx trainer Bushido T1980 inclusief skyliner De meest geavanceerde ergotrainer van dit moment een combinatie van een knap staaltje techniek en dito design. De Bushido is volkomen draadloos. Je hebt geen netspanning meer nodig en kunt op elke plek gaan staan waar je maar wilt. Het grafische display van de stuurcomputer toont alle benodigde informatie (vermogen, hartslag, cadans, snelheid, tijd, enzovoort). De menu's zijn zo opgebouwd dat je direct kunt starten. De Bushido heeft een krachtig remsysteem met een vliegwiel dat geheel is verwerkt in de behuizing. De rem is voorzien van led lampjes die bewegen op het ritme van je cadans. Zo wordt je prestatie op ludieke wijze gevisualiseerd.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895032061</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/508a2d6482f847d4a2acc609db198c2b.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>504.95</d2p1:PriceGross>
<d2p1:ProductCode>HTT1980</d2p1:ProductCode>
<d2p1:ProductGroup>77USA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometrainer</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>775.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T1980</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2011-07-05T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx trainer Genius MP T2000</d2p1:Description>
<d2p1:DescriptionLong>
Tacx trainer Genius Multi Player T2000 , De Genius Multiplayer, de combinatie van het robuuste, stevige frame en de geavanceerde Tacx Trainer software 4.0 , de Genius Multiplayer heeft een draadloze motorrem met computergestuurd vliegwiel die bij lage snelheden hoge vermogens genereert. Hiermee kunnen heuvels en bergen nog realistischer worden beklommen. De communicatie tussen rem, VR interface en PC verloopt draadloos de trainer heeft nog wel netspanning nodig. De Genius Multiplayer werkt met de ANT technologie en is daardoor ook te combineren met een vermogens- of cadansmeter of andere ANT applicaties. De trainer wordt geleverd met het nieuwe BlackTrack stuurframe. Inclusief 1 jaar Multiplayer licentie.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895037240</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/d2ae9fbf956248dc84a3f1d70fd97cbf.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>801.37</d2p1:PriceGross>
<d2p1:ProductCode>HTT2000</d2p1:ProductCode>
<d2p1:ProductGroup>77USA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometrainer</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>1229.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T2000</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
ProductCode nvarchar(255) Checked
Blocked nvarchar(255) Checked
DateCreated datetime Checked
Unit nvarchar(255) Checked
DeliveredPer float Checked
UnitAlternative nvarchar(255) Checked
Description nvarchar(255) Checked
ProductGroupCategory nvarchar(255) Checked
ProductGroup nvarchar(255) Checked
ProductGroupLevel1 nvarchar(255) Checked
ProductGroupLevel2 nvarchar(255) Checked
ProductGroupLevel3 nvarchar(255) Checked
ProductGroupLevel4 nvarchar(255) Checked
EANCode float Checked
PackageQuantity float Checked
DescriptionLong nvarchar(MAX)Checked
RecommendedSalesPrice float Checked
PriceGross float Checked
SupplierProductNumber nvarchar(255) Checked
VAT nvarchar(255) Checked
Bike nvarchar(255) Checked
StockMin float Checked
StockMax float Checked
Autorisation nvarchar(255) Checked
KboGroup nvarchar(255) Checked
Image nvarchar(255) Checked
Attributes nvarchar(255) Checked
Toegevoegd datetime Checked
Gewijzigd datetime Checked
June 19, 2014 at 9:17 am
Is the XML in proper format ? When I tried to assign it to a xml variable it threw an error
Declare @x xml='Your XML'
Select @x
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 21, 2014 at 6:53 am
pk2dpvp (6/19/2014)
Anyone has any suggestions? ^^I found loads of things but nothing seems to work...
I'm trying to get a link with XML data inside the page into a table but I can't find anything
It is only a question of getting the namespaces right. Note that the datatypes may need to be corrected;-)
😎
DECLARE @TXML XML = N'<ProductsResponse xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Operations">
<Products xmlns:d2p1="http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Types">
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2007-12-27T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx stuurinrichting VR</d2p1:Description>
<d2p1:DescriptionLong>
Tacx Virtual Reality stuurframe , Wordt standaard geleverd bij de Fortius Multiplayer en is als optie leverbaar bij de andere twee VR trainers. Om het dynamische en interactieve spelelement optimaal te ervaren is het VR stuurframe een absolute must. Elke beweging van het stuur is direct zichtbaar op het scherm.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895016436</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/ffc7517a358342a4aaf5d3653f9d5095.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>97.90</d2p1:PriceGross>
<d2p1:ProductCode>HTT1905</d2p1:ProductCode>
<d2p1:ProductGroup>77UVA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometraineraccessoires</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>178.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T1905</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2008-11-19T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx trainer Bushido/skyliner T1980</d2p1:Description>
<d2p1:DescriptionLong>
Tacx trainer Bushido T1980 inclusief skyliner De meest geavanceerde ergotrainer van dit moment een combinatie van een knap staaltje techniek en dito design. De Bushido is volkomen draadloos. Je hebt geen netspanning meer nodig en kunt op elke plek gaan staan waar je maar wilt. Het grafische display van de stuurcomputer toont alle benodigde informatie (vermogen, hartslag, cadans, snelheid, tijd, enzovoort). De menu''s zijn zo opgebouwd dat je direct kunt starten. De Bushido heeft een krachtig remsysteem met een vliegwiel dat geheel is verwerkt in de behuizing. De rem is voorzien van led lampjes die bewegen op het ritme van je cadans. Zo wordt je prestatie op ludieke wijze gevisualiseerd.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895032061</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/508a2d6482f847d4a2acc609db198c2b.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>504.95</d2p1:PriceGross>
<d2p1:ProductCode>HTT1980</d2p1:ProductCode>
<d2p1:ProductGroup>77USA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometrainer</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>775.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T1980</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
<d2p1:Product>
<d2p1:Attributes i:nil="true"/>
<d2p1:Autorisation>AA99</d2p1:Autorisation>
<d2p1:Bike>false</d2p1:Bike>
<d2p1:Blocked>false</d2p1:Blocked>
<d2p1:DateCreated>2011-07-05T00:00:00</d2p1:DateCreated>
<d2p1:DeliveredPer>1.000</d2p1:DeliveredPer>
<d2p1:Description>Tacx trainer Genius MP T2000</d2p1:Description>
<d2p1:DescriptionLong>
Tacx trainer Genius Multi Player T2000 , De Genius Multiplayer, de combinatie van het robuuste, stevige frame en de geavanceerde Tacx Trainer software 4.0 , de Genius Multiplayer heeft een draadloze motorrem met computergestuurd vliegwiel die bij lage snelheden hoge vermogens genereert. Hiermee kunnen heuvels en bergen nog realistischer worden beklommen. De communicatie tussen rem, VR interface en PC verloopt draadloos de trainer heeft nog wel netspanning nodig. De Genius Multiplayer werkt met de ANT technologie en is daardoor ook te combineren met een vermogens- of cadansmeter of andere ANT applicaties. De trainer wordt geleverd met het nieuwe BlackTrack stuurframe. Inclusief 1 jaar Multiplayer licentie.
</d2p1:DescriptionLong>
<d2p1:EANCode>8714895037240</d2p1:EANCode>
<d2p1:Image>
http://217.114.111.140/d2ae9fbf956248dc84a3f1d70fd97cbf.jpg
</d2p1:Image>
<d2p1:KboGroup>KA46</d2p1:KboGroup>
<d2p1:PackageQuantity>1.000</d2p1:PackageQuantity>
<d2p1:PriceGross>801.37</d2p1:PriceGross>
<d2p1:ProductCode>HTT2000</d2p1:ProductCode>
<d2p1:ProductGroup>77USA</d2p1:ProductGroup>
<d2p1:ProductGroupCategory>KCU</d2p1:ProductGroupCategory>
<d2p1:ProductGroupLevel1>Trainers</d2p1:ProductGroupLevel1>
<d2p1:ProductGroupLevel2>Hometrainer</d2p1:ProductGroupLevel2>
<d2p1:ProductGroupLevel3 i:nil="true"/>
<d2p1:ProductGroupLevel4 i:nil="true"/>
<d2p1:RecommendedSalesPrice>1229.00</d2p1:RecommendedSalesPrice>
<d2p1:StockMax>3.000</d2p1:StockMax>
<d2p1:StockMin>1.000</d2p1:StockMin>
<d2p1:SupplierProductNumber>T2000</d2p1:SupplierProductNumber>
<d2p1:Unit>STK</d2p1:Unit>
<d2p1:UnitAlternative>STK</d2p1:UnitAlternative>
<d2p1:VAT>Hoog</d2p1:VAT>
</d2p1:Product>
</Products>
</ProductsResponse>';
--;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/Ultimate')
;WITH XMLNAMESPACES
(
'http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Operations' AS NS1
,DEFAULT 'http://schemas.datacontract.org/2004/07/Webservices.ServiceModel.Types'
)
SELECT
PRO.DUCT.value('Autorisation[1]','VARCHAR(10)') AS Autorisation
,PRO.DUCT.value('Bike[1]','VARCHAR(10)') AS Bike
,PRO.DUCT.value('Blocked[1]','VARCHAR(10)') AS Blocked
,PRO.DUCT.value('DateCreated[1]','DATETIME') AS DateCreated
,PRO.DUCT.value('DeliveredPer[1]','VARCHAR(10)') AS DeliveredPer
,PRO.DUCT.value('Description[1]','NVARCHAR(50)') AS Description
,PRO.DUCT.value('DescriptionLong[1]','NVARCHAR(1024)') AS DescriptionLong
,PRO.DUCT.value('EANCode[1]','VARCHAR(20)') AS EANCode
,PRO.DUCT.value('Image[1]','VARCHAR(150)') AS Image
,PRO.DUCT.value('KboGroup[1]','VARCHAR(10)') AS KboGroup
,PRO.DUCT.value('PackageQuantity[1]','VARCHAR(10)') AS PackageQuantity
,PRO.DUCT.value('PriceGross[1]','DECIMAL(18,4)') AS PriceGross
,PRO.DUCT.value('ProductCode[1]','VARCHAR(10)') AS ProductCode
,PRO.DUCT.value('ProductGroup[1]','VARCHAR(10)') AS ProductGroup
,PRO.DUCT.value('ProductGroupCategory[1]','VARCHAR(10)') AS ProductGroupCategory
,PRO.DUCT.value('ProductGroupLevel1[1]','VARCHAR(10)') AS ProductGroupLevel1
,PRO.DUCT.value('ProductGroupLevel2[1]','VARCHAR(10)') AS ProductGroupLevel2
,PRO.DUCT.value('ProductGroupLevel3[1]','VARCHAR(10)') AS ProductGroupLevel3
,PRO.DUCT.value('ProductGroupLevel4[1]','VARCHAR(10)') AS ProductGroupLevel4
,PRO.DUCT.value('RecommendedSalesPrice[1]','VARCHAR(10)') AS RecommendedSalesPrice
,PRO.DUCT.value('StockMax[1]','VARCHAR(10)') AS StockMax
,PRO.DUCT.value('StockMin[1]','VARCHAR(10)') AS StockMin
,PRO.DUCT.value('SupplierProductNumber[1]','VARCHAR(10)') AS SupplierProductNumber
,PRO.DUCT.value('Unit[1]','VARCHAR(10)') AS Unit
,PRO.DUCT.value('UnitAlternative[1]','VARCHAR(10)') AS UnitAlternative
,PRO.DUCT.value('VAT[1]','VARCHAR(10)') AS VAT
FROM @TXML.nodes('NS1:ProductsResponse/NS1:Products/Product') AS PRO(DUCT);
Results
Autorisation Bike Blocked DateCreated DeliveredPer Description DescriptionLong EANCode Image KboGroup PackageQuantity PriceGross ProductCode ProductGroup ProductGroupCategory ProductGroupLevel1 ProductGroupLevel2 ProductGroupLevel3 ProductGroupLevel4 RecommendedSalesPrice StockMax StockMin SupplierProductNumber Unit UnitAlternative VAT
------------ ---------- ---------- ----------------------- ------------ -------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------- ---------------------------------------------------------------------- --------------- ----------- ----------- ------------ -------------------- ------------------ ------------------ ------------------ ------------------ --------------------- ---------- ---------- --------------------- ---------- --------------- ----------
AA99 false false 2007-12-27 00:00:00.000 1.000 Tacx stuurinrichting VR Tacx Virtual Reality stuurframe , Wordt standaard geleverd bij de Fortius Multiplayer en is als optie leverbaar bij de andere twee VR trainers. Om he 8714895016436 http://217.114.111.140/ffc7517a358342a4aaf5d3653f9d5095.jpg KA46 1.000 97.9000 HTT1905 77UVA KCU Trainers Hometraine 178.00 3.000 1.000 T1905 STK STK Hoog
AA99 false false 2008-11-19 00:00:00.000 1.000 Tacx trainer Bushido/skyliner T1980 Tacx trainer Bushido T1980 inclusief skyliner De meest geavanceerde ergotrainer van dit moment een combinatie van een knap staaltje techniek en dito 8714895032061 http://217.114.111.140/508a2d6482f847d4a2acc609db198c2b.jpg KA46 1.000 504.9500 HTT1980 77USA KCU Trainers Hometraine 775.00 3.000 1.000 T1980 STK STK Hoog
AA99 false false 2011-07-05 00:00:00.000 1.000 Tacx trainer Genius MP T2000 Tacx trainer Genius Multi Player T2000 , De Genius Multiplayer, de combinatie van het robuuste, stevige frame en de geavanceerde Tacx Trainer softwar 8714895037240 http://217.114.111.140/d2ae9fbf956248dc84a3f1d70fd97cbf.jpg KA46 1.000 801.3700 HTT2000 77USA KCU Trainers Hometraine 1229.00 3.000 1.000 T2000 STK STK Hoog
June 21, 2014 at 9:57 am
Here is how I am importing an XML file into an XML variable for processing. The key is the OPENROWSET BULK option. Hope this helps some.
declare @Filename nvarchar(256),
@SQLCmd nvarchar(max),
@SQLParm nvarchar(max);
set @Filename = N'C:\Test\LoginStd.xml';
set @SQLParm = N'@ImportXML XML OUTPUT';
set @SQLCmd = N'select @ImportXML = cast(BulkColumn as XML) from openrowset(BULK ''' + @Filename + N''', SINGLE_CLOB) x;'
exec sp_executesql @SQLCmd, @SQLParm, @ImportXML = @LoginStdXml OUTPUT;
June 21, 2014 at 10:02 am
Lynn Pettis (6/21/2014)
Here is how I am importing an XML file into an XML variable for processing. The key is the OPENROWSET BULK option. Hope this helps some.
declare @Filename nvarchar(256),
@SQLCmd nvarchar(max),
@SQLParm nvarchar(max);
set @Filename = N'C:\Test\LoginStd.xml';
set @SQLParm = N'@ImportXML XML OUTPUT';
set @SQLCmd = N'select @ImportXML = cast(BulkColumn as XML) from openrowset(BULK ''' + @Filename + N''', SINGLE_CLOB) x;'
exec sp_executesql @SQLCmd, @SQLParm, @ImportXML = @LoginStdXml OUTPUT;
Quick question Lynn, have you found any difference in performance between explicitly and implicitly casting to xml in the import?
😎
June 21, 2014 at 11:49 am
Eirikur Eiriksson (6/21/2014)
Lynn Pettis (6/21/2014)
Here is how I am importing an XML file into an XML variable for processing. The key is the OPENROWSET BULK option. Hope this helps some.
declare @Filename nvarchar(256),
@SQLCmd nvarchar(max),
@SQLParm nvarchar(max);
set @Filename = N'C:\Test\LoginStd.xml';
set @SQLParm = N'@ImportXML XML OUTPUT';
set @SQLCmd = N'select @ImportXML = cast(BulkColumn as XML) from openrowset(BULK ''' + @Filename + N''', SINGLE_CLOB) x;'
exec sp_executesql @SQLCmd, @SQLParm, @ImportXML = @LoginStdXml OUTPUT;
Quick question Lynn, have you found any difference in performance between explicitly and implicitly casting to xml in the import?
😎
First, missed part of the DECLAREs:
declare @LoginStdXml XML;
declare @iDoc int;
declare @Filename nvarchar(256),
@SQLCmd nvarchar(max),
@SQLParm nvarchar(max);
set @Filename = N'C:\Test\LoginStd.xml';
set @SQLParm = N'@ImportXML XML OUTPUT';
set @SQLCmd = N'select @ImportXML = cast(BulkColumn as XML) from openrowset(BULK ''' + @Filename + N''', SINGLE_CLOB) x;'
exec sp_executesql @SQLCmd, @SQLParm, @ImportXML = @LoginStdXml OUTPUT;
select @LoginStdXml
go
Second, I don't see any difference between using implicit or explicit conversion to XML.
June 24, 2014 at 1:43 am
so, I got the xml file
BUT... I can't import it because the LongDescription has thousands of characters each
so I was wondering if I was able to import everything BUT DescriptonLong
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
