March 24, 2010 at 12:14 pm
Hello,
I have an XML file and an XSD defined for it. I am importing this XML file into SQL Server 2005.
The SSIS package runs fine but there is no data in the table. I'm creating the table on the fly through SSIS itself.
When I click on preview of OLEDB Task, it shows no data but the correct columns. I checked my mappings and everything else.
I search online and found some reference to namespaces, is there some sort of special namespace I need to put on the XSD or XML to import into SQL Server?
Will appreciate any input I can get. I've been stuck on this for quite sometime now.
Thank you,
Slick
--
:hehe:
March 24, 2010 at 12:19 pm
Try creating the table first (outside of the package) and then importing, to try and narrow down where the error is occurring.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 24, 2010 at 12:29 pm
I tried that too, still no success.
--
:hehe:
March 24, 2010 at 12:33 pm
Phil,
Have you done an XML import into SQL Server? I honestly did not think it would be such a pain!
I have almost explored all options. This is now getting beyond challenging and burning me out like anything.
I have scoured the internet and either this is a very simple task that no one has explained or tutorialized or not many people choose to import data using XML files!
Ugh.
Any more ideas please?
Thanks,
S
--
:hehe:
March 24, 2010 at 12:37 pm
No I haven't - I've just read about other people's pain with XML. I try to stick with good old CSV's wherever possible. So I'm not the voice of experience here, I'll admit that.
Have you tried putting a Data Viewer after the XML datasource, to see whether data is actually being read in? It might give you some clues.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 24, 2010 at 12:41 pm
Yes, I put the data viewer and I dont see anything in the data viewer either.
I'm not sure why it's not grabbing the data from the XML file. I have the XML file path specified and also the XSD file path. Infact I generated the XSD using SSIS which did a great job at it though normalized it a great deal but accordingly to the XML hierarchial structure.
The file is only available in XML format, I tried asking for a flat file but was unsuccessful. A CSV would be even better. I guess i'll keep toying around with it.
After all, it shouldnt be rocket science. Hopefully..
Thank you for replying though.
Slick.
--
:hehe:
March 24, 2010 at 12:46 pm
March 24, 2010 at 12:49 pm
Let's see what we can do...
Do you have to use SSIS or would a T-SQL / XQuery solution work for you, too?
If the latter, please provide a (valid) XML snippet together with your expected result Edit: including the XSD file. I'm confident we'll find a solution.
March 24, 2010 at 12:56 pm
I want to automate this so thus the SSIS choice. But I'm sure I could use SQL task within SSIS to do xQuery right?
Below is xml file snippet:
<?xml version="1.0" encoding="UTF-8"?>
<Feed xmlns="http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3" name="XYZ" extractDate="2010-03-24T06:53:09.687-05:00">
<Product id="109" removed="false">
<Source>XYZ</Source>
<ExternalId>109
</ExternalId>
<Name>Men's / Women's Angora Wool Bed Socks</Name>
<Description>Don't lose another minute of sleep due to cold feet!Warm up to the sleep-inducing benefits of our angora / wool blend bed sock from Europe. Angora, one of the lightest wools available, provides three times the warmth of other wools without the itch.</Description><CategoryItems><CategoryItem id="7hao02nb7xpez5a7e3gloy8hx"><ExternalId>7hao02nb7xpez5a7e3gloy8hx</ExternalId><CategoryId>323728</CategoryId>
<CategoryName>Bed Socks</CategoryName>
</CategoryItem>
</CategoryItems>
<ProductPageUrl>abcde</ProductPageUrl>
<ProductReviewsUrl>abcde</ProductReviewsUrl>
<ImageUrl>abcde</ImageUrl>
<ReviewStatistics>
<AverageOverallRating>4.25</AverageOverallRating>
<OverallRatingRange>5</OverallRatingRange>
<TotalReviewCount>4</TotalReviewCount>
<RatingsOnlyReviewCount>1</RatingsOnlyReviewCount><RecommendedCount>3</RecommendedCount><AverageRatingValues/><RatingDistribution><RatingDistributionItem><RatingValue>3</RatingValue><Count>1</Count></RatingDistributionItem><RatingDistributionItem><RatingValue>4</RatingValue><Count>1</Count></RatingDistributionItem><RatingDistributionItem><RatingValue>5</RatingValue><Count>2</Count></RatingDistributionItem></RatingDistribution></ReviewStatistics><Reviews><Review id="12762918"><ModerationStatus>APPROVED</ModerationStatus><LastModificationTime>2009-11-19T13:55:40.000-06:00</LastModificationTime><Reviewer id="4785457"><ExternalId>4785457</ExternalId><DisplayName>anonymousdaughter2</DisplayName><Anonymous>false</Anonymous><DisplayLocale>en_US</DisplayLocale></Reviewer><RatingsOnly>false</RatingsOnly><Title>life saving socks</Title><ReviewText>My 86-year old mom's circulation was so poor that her feet had turned black. The doctor said she was too old and there was nothing he (surgically) could do. Since she started wearing these angora socks (day and night) along with some supplements like pomegranate juice, her feet have returned to pink. We are all amazed.</ReviewText><Rating>5</Rating><RatingRange>5</RatingRange><Recommended>true</Recommended><NumFeedbacks>1</NumFeedbacks><NumPositiveFeedbacks>1</NumPositiveFeedbacks><NumNegativeFeedbacks>0</NumNegativeFeedbacks><ReviewerLocation>Las Cruces NM</ReviewerLocation><DisplayLocale>en_US</DisplayLocale><SubmissionTime>2009-11-03T10:28:06.000-06:00</SubmissionTime><ProductReviewsUrl>http://reviews.footsmart.com/4007/109/reviews.htm?reviewID=12762918</ProductReviewsUrl><Featured>false</Featured><ContextDataValues><ContextDataValue id="August"><ExternalId>August</ExternalId><Label>August</Label><ContextDataDimension id="BirthdayMonth"><ExternalId>BirthdayMonth</ExternalId><Label>Your Birthday Month</Label></ContextDataDimension></ContextDataValue></ContextDataValues><RatingValues><RatingValue><Rating>3</Rating><RatingDimension id="TrueToSize" displayType="SLIDER"><ExternalId>TrueToSize</ExternalId><RatingRange>7</RatingRange><Label1>Length Runs Small</Label1><Label2>Length Runs Large</Label2></RatingDimension></RatingValue></RatingValues></Review><Review id="12707344"><ModerationStatus>APPROVED</ModerationStatus><LastModificationTime>2009-10-25T11:45:06.000-05:00</LastModificationTime><Reviewer id="5093566"><ExternalId>5093566</ExternalId><DisplayName>Jennysa</DisplayName><Anonymous>false</Anonymous><DisplayLocale>en_US</DisplayLocale></Reviewer><RatingsOnly>true</RatingsOnly><Rating>4</Rating><RatingRange>5</RatingRange><Recommended>true</Recommended><NumFeedbacks>0</NumFeedbacks><NumPositiveFeedbacks>0</NumPositiveFeedbacks><NumNegativeFeedbacks>0</NumNegativeFeedbacks><DisplayLocale>en_US</DisplayLocale><SubmissionTime>2009-10-24T09:33:59.000-05:00</SubmissionTime><ProductReviewsUrl>http://reviews.footsmart.com/4007/109/reviews.htm?reviewID=12707344</ProductReviewsUrl><Featured>false</Featured></Review><Review id="1958663"><ModerationStatus>APPROVED</ModerationStatus><LastModificationTime>2010-02-10T04:24:37.000-06:00</LastModificationTime><Reviewer id="3548257"><ExternalId>3548257</ExternalId><DisplayName>Georgia</DisplayName><Anonymous>false</Anonymous><DisplayLocale>en_US</DisplayLocale></Reviewer><RatingsOnly>false</RatingsOnly><Title>Angora Bedsocks, Superb On Every Level</Title><ReviewText>Inspired, as anyone frustrated by "one size fits all" socks will attest. Well-shaped heel fits snugly; toes have wiggle-room; blissfully soft and warm, and no angora-itch (how does Footsmart accomplish that?). Laundering by hand inside-out is simple; dry quickly in sun. One wearing told me I had a winner, and I ordered several pairs for gifts, receiving rave rreviews.</ReviewText><ProTags><Tag><Label>availability (no source other than footsmart</Label></Tag><Tag><Label>perfect height (just above ankle)</Label></Tag><Tag><Label>no binding at top</Label></Tag><Tag><Label>seam so placed that it doesn't irritate</Label></Tag></ProTags><Rating>5</Rating><RatingRange>5</RatingRange><Recommended>true</Recommended><NumFeedbacks>1</NumFeedbacks><NumPositiveFeedbacks>1</NumPositiveFeedbacks><NumNegativeFeedbacks>0</NumNegativeFeedbacks><ReviewerLocation>Augusta, Georgia</ReviewerLocation><DisplayLocale>en_US</DisplayLocale><SubmissionTime>2008-05-01T20:36:04.000-05:00</SubmissionTime><ProductReviewsUrl>http://reviews.footsmart.com/4007/109/reviews.htm?reviewID=1958663</ProductReviewsUrl><Featured>false</Featured></Review><Review id="13066214"><ModerationStatus>APPROVED</ModerationStatus><LastModificationTime>2009-12-23T14:15:03.000-06:00</LastModificationTime><Reviewer id="4984116"><ExternalId>4984116</ExternalId><DisplayName>jery</DisplayName><Anonymous>false</Anonymous><DisplayLocale>en_US</DisplayLocale></Reviewer><RatingsOnly>false</RatingsOnly><Title>nice but not worth the price</Title><ReviewText>These are comfortable but not worth the $$$. After one wash they "pill" on the outside.</ReviewText><Rating>3</Rating><RatingRange>5</RatingRange><Recommended>false</Recommended><NumFeedbacks>0</NumFeedbacks><NumPositiveFeedbacks>0</NumPositiveFeedbacks><NumNegativeFeedbacks>0</NumNegativeFeedbacks><ReviewerLocation>Virginia</ReviewerLocation><DisplayLocale>en_US</DisplayLocale><SubmissionTime>2009-12-23T06:16:39.000-06:00</SubmissionTime><ProductReviewsUrl>http://reviews.footsmart.com/4007/109/reviews.htm?reviewID=13066214</ProductReviewsUrl><Featured>false</Featured><ContextDataValues><ContextDataValue id="May"><ExternalId>May</ExternalId><Label>May</Label><ContextDataDimension id="BirthdayMonth"><ExternalId>BirthdayMonth</ExternalId><Label>Your Birthday Month</Label></ContextDataDimension></ContextDataValue></ContextDataValues><RatingValues><RatingValue><Rating>3</Rating><RatingDimension id="Comfort" displayType="SLIDER"><ExternalId>Comfort</ExternalId><RatingRange>7</RatingRange><Label1>Minimal Cushioning</Label1><Label2>Extra Cushioning</Label2></RatingDimension></RatingValue><RatingValue><Rating>3</Rating><RatingDimension id="TrueToSize" displayType="SLIDER"><ExternalId>TrueToSize</ExternalId><RatingRange>7</RatingRange><Label1>Length Runs Small</Label1><Label2>Length Runs Large</Label2></RatingDimension></RatingValue><RatingValue><Rating>3</Rating><RatingDimension id="Width" displayType="SLIDER"><ExternalId>Width</ExternalId><RatingRange>7</RatingRange><Label1>Width Runs Narrow</Label1><Label2>Width Runs Wide</Label2></RatingDimension></RatingValue><RatingValue><Rating>1</Rating><RatingDimension id="ArchSupport" displayType="SLIDER"><ExternalId>ArchSupport</ExternalId><RatingRange>7</RatingRange><Label1>No Arch Support</Label1><Label2>Great Arch Support</Label2></RatingDimension></RatingValue></RatingValues></Review></Reviews><Brand>Medi Comfort</Brand><ModelNumber>AWSBLU03</ModelNumber></Product></Feed>
Below is the XSD:
<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Feed">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Product">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Source" type="xs:string" />
<xs:element minOccurs="0" name="ExternalId" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Description" type="xs:string" />
<xs:element minOccurs="0" name="CategoryItems">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="CategoryItem">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:string" />
<xs:element minOccurs="0" name="CategoryId" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="CategoryName" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="ProductPageUrl" type="xs:string" />
<xs:element minOccurs="0" name="ProductReviewsUrl" type="xs:string" />
<xs:element minOccurs="0" name="ImageUrl" type="xs:string" />
<xs:element minOccurs="0" name="ReviewStatistics">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="AverageOverallRating" type="xs:decimal" />
<xs:element minOccurs="0" name="OverallRatingRange" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="TotalReviewCount" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="RatingsOnlyReviewCount" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="RecommendedCount" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="AverageRatingValues">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" maxOccurs="unbounded" name="AverageRatingValue">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="AverageRating" type="xs:decimal" />
<xs:element minOccurs="0" name="RatingDimension">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:string" />
<xs:element minOccurs="0" name="RatingRange" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Label1" type="xs:string" />
<xs:element minOccurs="0" name="Label2" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="displayType" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="RatingDistribution">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="RatingDistributionItem">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="RatingValue" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Count" type="xs:unsignedShort" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Reviews">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Review">
<xs:complexType>
<xs:sequence>
<xs:choice maxOccurs="unbounded">
<xs:element minOccurs="0" name="ModerationStatus" type="xs:string" />
<xs:element minOccurs="0" name="LastModificationTime" type="xs:dateTime" />
<xs:element minOccurs="0" name="Reviewer">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="DisplayName" type="xs:string" />
<xs:element minOccurs="0" name="Anonymous" type="xs:boolean" />
<xs:element minOccurs="0" name="DisplayLocale" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedInt" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="RatingsOnly" type="xs:boolean" />
<xs:element minOccurs="0" name="Title" type="xs:string" />
<xs:element minOccurs="0" name="ReviewText" type="xs:string" />
<xs:element minOccurs="0" name="ConTags">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Tag">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Label" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="ProTags">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Tag">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Label" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Rating" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="RatingRange" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Recommended" type="xs:boolean" />
<xs:element minOccurs="0" name="NumFeedbacks" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="NumPositiveFeedbacks" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="NumNegativeFeedbacks" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="ReviewerLocation" type="xs:string" />
<xs:element minOccurs="0" name="DisplayLocale" type="xs:string" />
<xs:element minOccurs="0" name="SubmissionTime" type="xs:dateTime" />
<xs:element minOccurs="0" name="ProductReviewsUrl" type="xs:string" />
<xs:element minOccurs="0" name="Featured" type="xs:boolean" />
<xs:element minOccurs="0" name="ContextDataValues">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ContextDataValue">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:string" />
<xs:element minOccurs="0" name="Label" type="xs:string" />
<xs:element minOccurs="0" name="ContextDataDimension">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:string" />
<xs:element minOccurs="0" name="Label" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="RatingValues">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="RatingValue">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Rating" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="RatingDimension">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ExternalId" type="xs:string" />
<xs:element minOccurs="0" name="RatingRange" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Label1" type="xs:string" />
<xs:element minOccurs="0" name="Label2" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="displayType" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Photos">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Photo">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Caption" type="xs:string" />
<xs:element minOccurs="0" name="Sizes">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Size">
<xs:complexType>
<xs:attribute name="id" type="xs:string" use="optional" />
<xs:attribute name="url" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedInt" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedInt" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Brand" type="xs:string" />
<xs:element minOccurs="0" name="UPC" type="xs:unsignedLong" />
<xs:element minOccurs="0" name="ModelNumber" type="xs:string" />
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedShort" use="optional" />
<xs:attribute name="removed" type="xs:boolean" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="name" type="xs:string" use="optional" />
<xs:attribute name="extractDate" type="xs:dateTime" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
Basically, take that data in the elements and attributes and dump it into a SQL table(s).
Thanks,
S
--
:hehe:
March 24, 2010 at 1:32 pm
Here's a sample code to populate three tables. Let me know if it helps and/or if you need any further assistance.
DECLARE @xml XML
SET @xml=(SELECT * FROM OPENROWSET(
BULK 'c:\temp\samplefile.xml',
SINGLE_BLOB) AS x)
-- products table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
c.value('@removed[1]','varchar(30)') AS removed,
c.value('Source[1]','varchar(30)') AS Source
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
-- category items table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
v.value('@id[1]','varchar(30)') AS CatId,
v.value('ExternalId[1]','varchar(30)') AS ExternalId
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
CROSS APPLY T.c.nodes('CategoryItems/CategoryItem') U(v)
-- reviews table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
v.value('@id[1]','varchar(30)') AS ReviewId,
v.value('ModerationStatus[1]','varchar(30)') AS ModerationStatus
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
CROSS APPLY T.c.nodes('Reviews/Review') U(v)
/* result set
idremovedSource
109falseXYZ
idCatIdExternalId
1097hao02nb7xpez5a7e3gloy8hx7hao02nb7xpez5a7e3gloy8hx
idReviewIdModerationStatus
10912762918APPROVED
10912707344APPROVED
1091958663APPROVED
10913066214APPROVED */
March 24, 2010 at 1:36 pm
Let me try it out.. thanks.
--
:hehe:
March 24, 2010 at 1:45 pm
hey Lutz,
I think this is the right direction. Firstly, could I integrate this into a SQL task on SSIS and run it? Also, I think you are just running SELECT queries against the XML file, could I run the SELECT and INSERT it into tables I will make now on the database?
Sorry for the novice questions, the query seems a little complicated to me. I have successfully executed it and I believe it produces the correct results.
Thank you,
Slick
--
:hehe:
March 24, 2010 at 2:37 pm
Okay I was able to INSERT it by adding an INSERT INTO tblName (Columns....) right before the SELECT clause.
Lutz, can you please tell me how I can add columns to these? The Product element had more child elements than just three and I want to show all (only the ones which did not have further children).
For example, in the query you posted, only id, removed and Source are shown. I want to show the Name, ExternalId, etc.. and insert them. I tried just adding those to the query but was unsuccessful.
Thank you so much for your help.
Regards,
Slick
--
:hehe:
March 24, 2010 at 2:44 pm
I guess I am really burned out. So sorry but it was such a dumb mistake.
I am now able to pull up all columns from the parent elements. I had "," before the FROM clause.
LOL... oh boy.
Thanks alot by the way. I could not have done it with out your help.
Regards,
Slick
--
:hehe:
March 24, 2010 at 2:49 pm
Slick,
I took Lutz' code, added the other product fields, and it works fine. One thing to remember, when dealing with XML, is that it is CaSe SeNsItIvE.
DECLARE @xml XML
SET @xml=(SELECT * FROM OPENROWSET(
BULK 'c:\temp\samplefile.xml',
SINGLE_BLOB) AS x)
-- products table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
c.value('@removed[1]','varchar(30)') AS removed,
c.value('Source[1]','varchar(30)') AS Source,
c.value('ExternalId[1]','varchar(10)') AS ExternalID,
c.value('Name[1]','varchar(100)') AS Name,
c.value('Description[1]','varchar(500)') AS Description,
c.value('CategoryName[1]','varchar(500)') AS CategoryName
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
-- category items table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
v.value('@id[1]','varchar(30)') AS CatId,
v.value('ExternalId[1]','varchar(30)') AS ExternalId
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
CROSS APPLY T.c.nodes('CategoryItems/CategoryItem') U(v)
-- reviews table
; WITH XMLNAMESPACES
(DEFAULT 'http://www.bazaarvoice.com/PRR/SyndicationFeed/1.3'
)
SELECT
c.value('@id[1]','int') AS id,
v.value('@id[1]','varchar(30)') AS ReviewId,
v.value('ModerationStatus[1]','varchar(30)') AS ModerationStatus
FROM @xml.nodes('Feed') A(b)
CROSS APPLY A.b.nodes('Product') T(c)
CROSS APPLY T.c.nodes('Reviews/Review') U(v)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply