December 30, 2009 at 7:47 am
Greetings all,
I have been doing some research on inserting an XML file into my database and so far I've been partially successful.
I have learned how to take the xsd (schema file) and place it in the xml schema collection of my SQL Server 2005 database;
I've created a temp table and dumped the entire xml document into the temp table and can view it so I know it's there;
I've managed to write my query and successfully reference the 'WITH XMLNAMESPACES and using my stored schema file;
I've reviewed how to write queries using XPath & XQuery and this is where I'm having a problem.
My query basically looks like this:
USE MyWebAppDB;
GO
; WITH XMLNAMESPACES (DEFAULT N'dbo', N'myXMLSchema' as myXMLTstTbl)
Insert into XMLTstTbl(
MyCol1,
MyCol2,
MyCol3,
MyCol4,
MyCol5,
MyCol6,
MyCol7
)
Select
tab.col.value('(country/countryid)[1]', 'int') as MyCol1,
tab.col.value('(country/countryname)[1]', 'nvarchar(75)') as MyCol2,
tab.col.value('(country/countrycontinent)[1]', 'nvarchar(50)') as MyCol3,
tab.col.value('(travel/lastname)[1]', 'nvarchar(75)') as MyCol4,
tab.col.value('(travel/firstname)[1]', 'nvarchar(75)') as MyCol5,
tab.col.value('(contact/phone)[1]', 'nvarchar(75)') as MyCol6,
tab.col.value('(contact/email)[1]', 'nvarchar(75)') as MyCol7
FROM XMLIMPORTTEST
CROSS APPLY
XML_DATA.NODES('XMLTstTbl') AS tab(col);
GO
Select * from XMLTstTbl;
I have tried a few variations on the Select statement but all iterations return zero.
Lastly, I did try to insert this XML Document using SSIS and was completely unsuccessful. I would get an ambiguous error so decided to try another path.
I appreciate any assistance as my head hurts from continually banging it on the desk trying to see what is most likely an obvious error! 🙂
December 30, 2009 at 8:25 am
I'd have to see a sample of the XML to be able to help with the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2010 at 1:47 am
Hi GSquared,
I can only provide a format sample of the xml data because the document is on an internal development system that is not connected to a network.
Here's a sample and thanks in advance for the help:
<ns:getPersonnel> (This is the top level tag of the XML Doc)
<ns:return> (Each record is within this tag)
<ax29:contact>
<ax29:address> address info here </ax29:address>
<ax29:email> email info here </ax29:email>
</ax29:contact>
<ax29:itinerary>
<ax29:country> country to visit here </ax29:country>
<ax29:dateoftrip> depart date </ax29:dateoftrip>
</ax29:itinerary>
<ax29:travelers>
<ax29:name> traveler name </ax29:name>
<ax29:title> traveler title </ax29:title>
</ax29:travelers>
</ns:return>
</ns:getPersonnel>
January 4, 2010 at 4:40 am
Your xml should include the namespace declaration in the first row. Something like
<ns:getPersonnel xmlns:ns="http://www.w3.org/2001/XMLSchema-instance" xmlns:ax29="uri">
Then you could query your data as follows:
;WITH xmlnamespaces(
DEFAULT 'uri' ,
'http://www.w3.org/2001/XMLSchema-instance' AS ns,
'uri' AS ax29)
SELECT
tab.col.value('(country/countryid)[1]', 'int') as MyCol1,
tab.col.value('(country/countryname)[1]', 'nvarchar(75)') as MyCol2,
tab.col.value('(country/countrycontinent)[1]', 'nvarchar(50)') as MyCol3,
tab.col.value('(travel/lastname)[1]', 'nvarchar(75)') as MyCol4,
tab.col.value('(travel/firstname)[1]', 'nvarchar(75)') as MyCol5,
tab.col.value('(contact/phone)[1]', 'nvarchar(75)') as MyCol6,
tab.col.value('(contact/email)[1]', 'nvarchar(75)') as MyCol7
FROM XMLIMPORTTEST
CROSS APPLY
XML_DATA.NODES('XMLTstTbl') AS tab(col);
January 4, 2010 at 5:11 am
Hi Imu92,
A question for you, if my schema file is in an xml collection in my db, wouldn't the Default in the XMLNAMESPACES reference that object vice the url?
Thanks for your input!
January 4, 2010 at 5:52 am
The namespaces you need to declare are those used within your xml file.
In the given case it would be "ns:" and "ax29:".
The DEFAULT clause within the namespace declaration is the one you don't have to specifiy in your query. See the following example.
Side note: Unfortunately, the xml sample you provided doesn't match your query, resulting in a value for MyCol7 only.
DECLARE @xml xml
SET @xml='
<ns:getPersonnel xmlns:ns="http://www.w3.org/2001/XMLSchema-instance" xmlns:ax29="uri">
<ns:return>
<ax29:contact>
<ax29:address> address info here </ax29:address>
<ax29:email> email info here </ax29:email>
</ax29:contact>
<ax29:itinerary>
<ax29:country> country to visit here </ax29:country>
<ax29:dateoftrip> depart date </ax29:dateoftrip>
</ax29:itinerary>
<ax29:travelers>
<ax29:name> traveler name </ax29:name>
<ax29:title> traveler title </ax29:title>
</ax29:travelers>
</ns:return>
</ns:getPersonnel>
'
-- query 1: default namespace is ns, so ax29 needs to be named
;WITH xmlnamespaces(
DEFAULT 'http://www.w3.org/2001/XMLSchema-instance' ,
'http://www.w3.org/2001/XMLSchema-instance' AS ns,
'uri' AS ax29)
SELECT
tab.col.value('(country/countryid)[1]', 'int') as MyCol1,
tab.col.value('(country/countryname)[1]', 'nvarchar(75)') as MyCol2,
tab.col.value('(country/countrycontinent)[1]', 'nvarchar(50)') as MyCol3,
tab.col.value('(travel/lastname)[1]', 'nvarchar(75)') as MyCol4,
tab.col.value('(travel/firstname)[1]', 'nvarchar(75)') as MyCol5,
tab.col.value('(contact/phone)[1]', 'nvarchar(75)') as MyCol6,
tab.col.value('(ax29:contact/ax29:email)[1]', 'nvarchar(75)') as MyCol7
FROM @xml.nodes('getPersonnel/return') AS tab(col);
-- query 2: default namespace is ax29 , so ns needs to be named
;WITH xmlnamespaces(
DEFAULT 'uri' ,
'http://www.w3.org/2001/XMLSchema-instance' AS ns,
'uri' AS ax29)
SELECT
tab.col.value('(country/countryid)[1]', 'int') as MyCol1,
tab.col.value('(country/countryname)[1]', 'nvarchar(75)') as MyCol2,
tab.col.value('(country/countrycontinent)[1]', 'nvarchar(50)') as MyCol3,
tab.col.value('(travel/lastname)[1]', 'nvarchar(75)') as MyCol4,
tab.col.value('(travel/firstname)[1]', 'nvarchar(75)') as MyCol5,
tab.col.value('(contact/phone)[1]', 'nvarchar(75)') as MyCol6,
tab.col.value('(contact/email)[1]', 'nvarchar(75)') as MyCol7
FROM @xml.nodes('ns:getPersonnel/ns:return') AS tab(col);
-- query 3: no default namespace, so ns and ax29 need to be named
;WITH xmlnamespaces(
'http://www.w3.org/2001/XMLSchema-instance' AS ns,
'uri' AS ax29)
SELECT
tab.col.value('(country/countryid)[1]', 'int') as MyCol1,
tab.col.value('(country/countryname)[1]', 'nvarchar(75)') as MyCol2,
tab.col.value('(country/countrycontinent)[1]', 'nvarchar(50)') as MyCol3,
tab.col.value('(travel/lastname)[1]', 'nvarchar(75)') as MyCol4,
tab.col.value('(travel/firstname)[1]', 'nvarchar(75)') as MyCol5,
tab.col.value('(contact/phone)[1]', 'nvarchar(75)') as MyCol6,
tab.col.value('(ax29:contact/ax29:email)[1]', 'nvarchar(75)') as MyCol7
FROM @xml.nodes('ns:getPersonnel/ns:return') AS tab(col);
January 5, 2010 at 12:24 am
Imu92
EUREKA! It works. Thanks much. I only had to tweek it a bit but I can't say thanks enough.
January 5, 2010 at 1:29 am
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply