Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ltgrady
Starting Member
9 Posts |
 Posted - 2007-09-20 : 23:36:47
|
| Here is my XML doc and the expression i'm using to try and insert into a table. Instead of getting 10 records in my table, I'm getting 1 record with the 10 values concatonated.DECLARE @msgBody XMLSET @msgBody='<AuditMsg MsgType="Search"> <Search> <userID>CE3AEFE6-F9CE-4109-8430-15C8463DF215</userID> <brands> <brandID>C6BE3786-09B6-41AB-BA10-C72575DAB637</brandID> <brandID>41E99CDA-C780-442F-B65A-281B7176E1B2</brandID> <brandID>EBA97FC5-400F-4B7D-A629-E6D68DA8BDD9</brandID> </brands> <subCats> <subCatID>1</subCatID> <subCatID>4</subCatID> <subCatID>45</subCatID> <subCatID>56</subCatID> <subCatID>1</subCatID> <subCatID>342</subCatID> <subCatID>7</subCatID> <subCatID>28</subCatID> <subCatID>93</subCatID> <subCatID>423</subCatID> </subCats> <keywords>travel</keywords> <priceFrom>10</priceFrom> <priceTo>100</priceTo> <hiRes>-1</hiRes> <source>Search</source> </Search></AuditMsg>'INSERT INTO tblAudit_Search('Name',SubCat,Audit_ID)SELECT 'TEST',T.c.value('subCatID[1]','varchar(50)'),NEWID() FROM @msgBody.nodes('AuditMsg/Search/subCats') T(c)What I'm looking for are ten records in my tblAudit_Search table, one for each subcategory. What I'm getting is one record with all ten of those values concatonated together in the SubCat field.I've tried a bunch of different options (including trying to use T.C.query instead of T.c.value. But nothing is working.What am I doing wrong? Thanks |
|
|
ltgrady
Starting Member
9 Posts |
Posted - 2007-09-20 : 23:58:03
|
| DOH! Nevermind.I need to call FROM @msgBody.nodes('AuditMsg/Search/subCats/*') T(c)The asterisk makes all the difference :) |
 |
|
|
|
|
|
XQuery insert, only 1 record returning?