1

I know this question has been asked by few other members here but they have slightly different needs and I can't relate them exactly to my situation. So apologies for asking this again.

I have the following XML file (this is just the fist part of it) and I need to convert it to a SQL Server table.

How do I actually do this? I am a total beginner yet trying to do somewhat a big project. I don't know much SQL and I have absolutely no idea how to read an XML file through SQL server. I want the CodeType element also to go as a column and reflect the corresponding values

<ReferenceDataItems CodeType="AboriginalOrTorresStraitIslanderOrigin">
  <Item>
    <CodeValue>NO</CodeValue>
    <CodeDescription>No</CodeDescription>
    <OrderNumber>1</OrderNumber>
  </Item>
  <Item>
    <Item>
      <CodeValue>TSI</CodeValue>
      <CodeDescription>Torres Strait Islander</CodeDescription>
      <OrderNumber>3</OrderNumber>
    </Item>
    <Item>
      <CodeValue>BOTH</CodeValue>
      <CodeDescription>Aboriginal and Torres Strait Islander</CodeDescription>
      <OrderNumber>4</OrderNumber>
    </Item>
    <Item>
      <CodeValue>NOTSTATED</CodeValue>
      <CodeDescription>Not stated/Inadequately described</CodeDescription>
      <OrderNumber>5</OrderNumber>
    </Item>
</ReferenceDataItems>
<ReferenceDataItems CodeType="AccommodationType">
  <Item>
    <CodeValue>BOARDING</CodeValue>
    <CodeDescription>Boarding house</CodeDescription>
    <OrderNumber>1</OrderNumber>
  </Item>
  <Item>
    <CodeValue>TRANSITION</CodeValue>
    <CodeDescription>Crisis, emergency or transition</CodeDescription>
    <OrderNumber>2</OrderNumber>
  </Item>
  <Item>
    <CodeValue>LIVINGUNIT</CodeValue>
    <CodeDescription>Independent living unit</CodeDescription>
    <OrderNumber>3</OrderNumber>
  </Item>
  <Item>
    <CodeValue>COMMUNITY</CodeValue>
    <CodeDescription>Indigenous community/settlement</CodeDescription>
    <OrderNumber>4</OrderNumber>
  </Item>
  <Item>
    <CodeValue>INSTITUTION</CodeValue>
    <CodeDescription>Institutional setting (i.e. residential aged care, hospital)</CodeDescription>
    <OrderNumber>5</OrderNumber>
  </Item>
  <Item>
    <CodeValue>CLIENTOWNED</CodeValue>
    <CodeDescription>Private residence - client or family owned/purchasing</CodeDescription>
    <OrderNumber>6</OrderNumber>
  </Item>
  <Item>
    <CodeValue>PRIVATERENTAL</CodeValue>
    <CodeDescription>Private residence - private rental</CodeDescription>
    <OrderNumber>7</OrderNumber>
  </Item>
  <Item>
    <CodeValue>PUBLICRENTAL</CodeValue>
    <CodeDescription>Private residence - public rental</CodeDescription>
    <OrderNumber>8</OrderNumber>
  </Item>
  <Item>
    <CodeValue>PUBLICSHELTER</CodeValue>
    <CodeDescription>Public shelter</CodeDescription>
    <OrderNumber>9</OrderNumber>
  </Item>
  <Item>
    <CodeValue>SUPPORTED</CodeValue>
    <CodeDescription>Supported accommodation</CodeDescription>
    <OrderNumber>10</OrderNumber>
  </Item>
  <Item>
    <CodeValue>NOTSTATED</CodeValue>
    <CodeDescription>Not stated</CodeDescription>
    <OrderNumber>11</OrderNumber>
  </Item>
  <Item>
    <CodeValue>OTHER</CodeValue>
    <CodeDescription>Other</CodeDescription>
    <OrderNumber>12</OrderNumber>
  </Item>
</ReferenceDataItems>
Improve this question

1 Answer 1

Reset to default
1

Assuming you have your entire XML data in a variable called @xml, then you can use this XQuery code to extract the details information:

SELECT
    CodeType = XC.value('@CodeType', 'varchar(50)'),
    CodeValue = XC2.value('(CodeValue)[1]', 'varchar(100)'),
    CodeDescription = XC2.value('(CodeDescription)[1]', 'varchar(100)'),
    OrderNumber = XC2.value('(OrderNumber)[1]', 'int')
FROM
    @xml.nodes('/ReferenceDataItems') AS XT(XC)
CROSS APPLY
    XC.nodes('Item') AS XT2(XC2)

This will return a dataset something like this:

enter image description here

How you want to insert this into which table is not clear from your question - you need to figure that out yourself.....

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you so much! First of all, sorry I don't know how to accept the edits you performed on my post. I pressed rollback and it went to the old post style. Sorry as I was saying I'm new to the game. Should I run SQL server and fire up this query as a new query? I want to put them in to a table call 'ReferenceData' Also, is this where I suppose to put the path of the XML file? ('/ReferenceDataItems')
@RandikaWijekoon: you don't need to accept anything - just leave the post as it is - edited and more readable. DO NOT rollback.....
(1) you need to load the XML into a SQL Server variable - Google for that, there's tons of resources showing you how to do this. Then (2) you need to be sure you have all the necessary columns in your ReferenceData table, and then (3) you can use INSERT INTO dbo.ReferenceData(CodeType, CodeValue, CodeDescription, OrderNumber) SELECT ...... (and use the SELECT above as the source for your INSERT statement)
Thanks so much Marc! You are a champion! I was going to manually type the data :) . This saves so much time and I learnt something new. Much appreciated my friend!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.