Converting XML to SQL Server Database

Question

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>

Answer 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:

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