Sunday, March 10, 2013

[T-SQL] XML with NameSpaces in each Node.

[T-SQL] XML with NameSpaces in each Node.


XML with NameSpaces in each Node.

Posted: 09 Mar 2013 10:29 PM PST

HI Guys,I came to a situation where I have to load XML file in SQL server tables. I have worked in past to load XML using node method but It was without namespace. Please help me. Below is my XML.<ns1:Post xmlns:ns1="DataFor"> <ApplicantEntryComplete>false</ApplicantEntryComplete> <AssistantUnderwriter>davisg</AssistantUnderwriter> <CATTIV_USD>0</CATTIV_USD> <CreateApplication>IIF</CreateApplication> <ns1:Brokerage xmlns:ns1="DataFor"> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <numberOfEmployees>0</numberOfEmployees> <ns1:Contact xmlns:ns1="DataFor"> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessAffiliation>McGriff, Seibels & Williams</BusinessAffiliation> <Salutation>Mr.</Salutation> <pxCreateOperator>VimalrajM</pxCreateOperator> </ns1:Contact> </ns1:Brokerage> <ns1:Policy xmlns:ns1="DataFor"> <SLADate>2013-05-24</SLADate> <SLADatePol>2013-03-06</SLADatePol> <SLADateRen>2013-03-06</SLADateRen> </ns1:Policy> <ns1:Post xmlns:ns1="DataFor"> <CompletionStatus>Complete</CompletionStatus> <PlacementType>W</PlacementType> <ProductClass>Excess</ProductClass> <StatusDate>2013-03-01</StatusDate> <PostDate>2013-03-01T05:00:00.000Z</PostDate> <PostID>DX7430701S</PostID> <PostStatus>Converted</PostStatus> </ns1:Post></ns1:Post>

Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL

Posted: 09 Mar 2013 07:35 AM PST

I have a Menu table with a structure like this:MenuID, MenuDesc, ParentMenuID, SequenceThe Menu is for a packaged bit of software, and i have no ability to modify the table structure. This table has 1682 rows, and a maximum of 5 levels. Sequence field is an ordering integer that is unique to each parentMenuID subset, but that can repeat for different parentMenuID subsets.I am trying to write a query to extract this data into Excel so that the order is correct and the levels are identified.For example, take this dataset:[b]MenuID,MenuDesc,Parent Menu,Sequence100,Main Menu,[null],1200,Sales Management,100,1300,Customer Relationship Management,200,1400,Setup,300,1500,Attribute,400,1515,Call Type,400,2504,Competitor,400,3410,General Operations,300,2521,Customer/Contact Import,410,1550,Mobile Connect Conflicts,410,2560,Mobile Connect Sync,410,3305,Help Desk,200,2[/b]I need to write a query that will return the Order (that is the order of the entire expanded tree), and the level (1-5), like this:[b]MenuID,MenuDesc,Parent Menu,Sequence,Order,Level100,Main Menu,[null],1,1,1200,Sales Management,100,1,2,2300,Customer Relationship Management,200,1,3,3400,Setup,300,1,4,4500,Attribute,400,1,5,5515,Call Type,400,2,6,5504,Competitor,400,3,7,5410,General Operations,300,2,8,4521,Customer/Contact Import,410,1,9,5550,Mobile Connect Conflicts,410,2,10,5560,Mobile Connect Sync,410,3,11,5305,Help Desk,200,2,12,3[/b]I am banging my head against the wall trying to get a query to give me this. :w00t: Any help or ideas would be extremely appreciated!Thanks,Keith

No comments:

Post a Comment

Search This Blog