मुझे SQL सर्वर एक्सप्रेस 2005 में कुछ एक्सएमएल डेटा आयात करने की ज़रूरत है और मैं सोच रहा हूं कि इसे करने का तरीका क्या होगा? नीचे एक्सएमएल का एक नमूना है जिसे मुझे आयात करने की आवश्यकता है। अगर मुझे करना है तो मैं शायद इसे करने के लिए कुछ कोड लिख सकता हूं। लेकिन मैं एक आसान तरीका पसंद करूंगा। मैं एसक्यूएल सर्वर एक्सप्रेस 2005 का उपयोग कर रहा हूं, ऐसा लगता है कि यह एसएसआईएस का उपयोग करने से बाहर है।

<?xml version="1.0" encoding="UTF-8" ?>
<tour>
  <tourName>Highlights of Jordan</tourName>
  <dossierCode>DWHJ</dossierCode>
  <tripDescription>Explore ancient sites and lost cities, overnight in a Bedouin tent and dine with the locals amidst the dramatic desert scenery of Wadi Rum. Take in Petra's fascinating history and marvel at its thrilling beauty—and still have a chance to relax in the therapeutic waters of the Dead Sea. This eight-day trip is ideal for the traveller who is short on time but wants to soak up the colours, culture and history of a country that's small in size but big in adventure.</tripDescription>
  <tripStyle>Classic</tripStyle>
  <year>2011</year>
  <brochureSupplier>Gap Adventures</brochureSupplier>
  <tripCountries>Jordan</tripCountries>
  <tripContinents>North Africa / Middle East</tripContinents>
  <startCountry>Jordan</startCountry>
  <finishCountry>Jordan</finishCountry>
  <startCity>Amman</startCity>
  <finishCity>Amman</finishCity>
  <tripActivities>Culture/History</tripActivities>
  <duration>8</duration>
  <physicalGrading>3</physicalGrading>
  <serviceLevel>Standard</serviceLevel>  
  <groupSize>
    <minimum>5</minimum>
    <maximum>15</maximum>
  </groupSize>
  <briefItinerary>
    <briefItineraryItem label="Day 1 Amman">Arrive at any time.</briefItineraryItem>
    <briefItineraryItem label="Day 2 Jerash, the Dead Sea (B)">Guided visit to the ancient Roman town of Jerash, trip to Dead Sea.</briefItineraryItem>
    <briefItineraryItem label="Days 3-4 Petra (2B)">Madaba mosaics, Mount Nebo, Kerak Castle, Petra entrance and guided tour.</briefItineraryItem>
    <briefItineraryItem label="Day 5 Wadi Rum (B,D)">Wadi Rum 4x4 jeep excursion, dinner and night in desert camp.</briefItineraryItem>
    <briefItineraryItem label="Day 6 Aqaba (B)">Optional snorkelling excursion.</briefItineraryItem>
    <briefItineraryItem label="Day 7 Amman (B)">Time to explore Amman.</briefItineraryItem>
    <briefItineraryItem label="Day 8 Amman (B)">Depart at any time.</briefItineraryItem>
  </briefItinerary>
  <detailedItinerary>
    <detailedItineraryItem label="Day 1 Arrive Amman">You are welcome to arrive in Amman at any time, as today is a designated arrival day with no planned activities. Please note that there will be an important welcome meeting this evening at our joining hotel. Please check the hotel notice board for more information on the location of this meeting or ask at reception. Please bring your passport and travel insurance documents to this meeting, as you will be required to fill out some important pre-trip admin documents. After learning more about our tour and plans for the following day, you are welcome to join our leader and your travelling companions for an optional dinner nearby. Overnight in Amman.</detailedItineraryItem>
    <detailedItineraryItem label="Day 2 Jerash, Dead Sea (B)">After breakfast at the hotel we will set off on our half day trip to Jerash, a fascinating place and one of the largest and most well preserved Roman sites outside Italy. Its paved and colonnaded streets, soaring hilltop temples, handsome theatres, spacious public squares and plazas, baths, fountains and city walls pierced by towers and gates remain in exceptional condition. The drive takes less than an hour each way, but will transport you some 2000 years back in time. After lunch, we head to the Dead Sea. The Dead Sea is actually a lake, without any from of life whatsoever existing in its waters. The water is so dense that it is practically impossible to sink, being five to ten times as salty as regular sea water. We base ourselves at a resort for the day, where you will have access to the beach. While swimming, cover yourself with the sea's mud, which is reputed to have special healing powers, and don't forget to take a photo of yourself reading a book while floating! You will notice that the salt penetrates any cuts on your body and has an unusual smell, but there are showers to wash off under after swimming. Afterwards, while away under an umbrella next to one of the fresh water swimming pools. There are a few options for your lunch today and the choice is yours, ranging from a full buffet at the resort or a snack from the cafe. We will return to Amman for the evening. Overnight in Amman. Approx travel times: Amman to Jerash, 2hrs (return); Amman to Dead Sea, 4hrs (return)</detailedItineraryItem>
  </detailedItinerary>
</tour>
0
SuperFurryToad 15 फरवरी 2011, 15:48

3 जवाब

सबसे बढ़िया उत्तर

OPENROWSET बल्क कीवर्ड का समर्थन करता है जो हमें sql सर्वर 2005 में xml फ़ाइलों को आयात करने में मदद करता है। यहाँ एक उदाहरण है जो मुझे मिला:

CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
SELECT  * 
FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest

DROP TABLE XmlImportTest

संदर्भ

0
reggie 15 फरवरी 2011, 16:03
क्या डेटा को तोड़ने और इसे विशिष्ट क्षेत्रों में डंप करने का कोई तरीका है?
 – 
SuperFurryToad
15 फरवरी 2011, 16:48
stackoverflow.com/ प्रश्न/1679777/… यह प्रश्न स्टैक ओवरफ़्लो पर मिला।
 – 
reggie
15 फरवरी 2011, 17:19

आप यहां पढ़ सकते हैं http://msdn.microsoft SQL सर्वर 2005 में xml समर्थन के बारे में .com/en-us/library/ms345117%28v=sql.90%29.aspx

कुछ कोड दिखा रहा है कि आप क्या कर सकते हैं।

declare @xml as xml

set @xml = 
'<?xml version="1.0" encoding="UTF-8" ?>
<tour>
  <tourName>Highlights of Jordan</tourName>
  <dossierCode>DWHJ</dossierCode>
  <tripStyle>Classic</tripStyle>
  <year>2011</year>
</tour>  
'

select 
  t.value('tourName[1]', 'varchar(50)'),
  t.value('dossierCode[1]', 'varchar(50)'),
  t.value('tripStyle[1]', 'varchar(50)'),
  t.value('year[1]', 'int')
from @xml.nodes('tour') t(t)
0
Mikael Eriksson 15 फरवरी 2011, 17:05

आप प्रतिक्रियाओं के लिए धन्यवाद। अंत में, मैंने अभी XML को पार्स करने के लिए कुछ कोड लिखा है, और एक बल्क इंसर्ट किया है

0
SuperFurryToad 18 फरवरी 2011, 17:22