XML with NameSpaces in each Node.

• krishnasavalia

  SSC Veteran

  Points: 230

  More actions

  March 10, 2013 at 6:29 am

  #275096

  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>

• Jason Wolfkill

  SSCrazy Eights

  Points: 9832

  More actions

  March 11, 2013 at 9:34 am

  #1595627

  krishnasavalia (3/10/2013)

  ---

  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>

  SQL Server supports XML namespaces. To shred your XML with the nodes() method, just include the namespace declaration in the XQuery argument and use the namespace prefix in the XQuery path, like this example from Books Online :

  SELECT C.query('.') as result

  FROM Production.ProductModel

  CROSS APPLY Instructions.nodes('

  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

  /MI:root/MI:Location') as T(C)

  WHERE ProductModelID=7

  Jason Wolfkill