SQL OpenXML Multiple Tag Issue

Question

I am just new to reading in an XML file into a table through SQL Server Management Studio. There are probably better ways but I would like to use this approach.

Currently I am reading in a standard XML file of records on people. A <record> tag is the highest level of each row of data. I want to read all the records into separate rows into my SQL table.

I have gotten along fine so far using the following approach as follows:

SELECT
        -- Record
        category, editor, entered, subcategory, uid, updated,
        -- Person
        first_name, last_name, ssn, ei, title, POSITION,
FROM OPENXML(@hDoc, 'records/record/person/names')
WITH 
(
    -- Record
    category [varchar](100) '../../@category',
    editor [varchar](100) '../../@editor',
    entered Datetime '../../@entered',
    subcategory [varchar](100) '../../@subcategory',
    uid BIGINT '../../@uid',
    updated [varchar](100) '../../@updated',
    -- Person
    first_name [varchar](100) 'first_name',
    last_name [varchar](100) 'last_name',
    ssn [varchar](100) '../@ssn',
    ei [varchar](100) '../@e-i',
    title [varchar](100) '../title',
    Position [varchar](100) '../position',  
)

However this approach has worked fine as the tag names have all been unique to each record/person. The issue I have is within the <Person> tag I now have an <Aliases> tag that contains a list of more than 1 <Alias> test name </Alias> tags. If I use the above approach & reference '../aliases' I get all the Alias elements as one long String row mixed together. If I just try '../aliases/alias' ONLY the first element is returned per record row. If there was 10 Alias elements within the Aliases tag set I would like 10 rows returned for example.

Is there a way to specify that when there are multiple tags of the same name within a higher level tag, return them all & not just one row?

The following is the example block within the XML I am referring to:

- <aliases>
  <alias>test 1</alias> 
  <alias>test 2</alias> 
  <alias>test 3</alias> 
  </aliases>

I would like the following in the SQL table:

Record               Aliases

Record 1             test 1
Record 1             test 2
Record 1             test 3
Record 2             test 4
Record 2             test 5

but all I get is:

Record 1             test 1 
Record 2             test 4

Apologies if I have not explained this correctly - any help would be greatly appreciated.

Occidentalize

Did you read this? technet.microsoft.com/en-us/library/ms186918.aspx – Phylogeny

Can you test it with OPENXML(@hDoc, 'records/record/person/names',2)? – Phylogeny

Thank you for your reply. I have tried OPENXML(@hDoc, 'records/record/person/names',2) but all the alias elements are being added as one long string per record row. I have read the link you provided & can see why you suggested that approach which looks like it should have worked. – Occidentalize

Found this link which solved the problem - #8013536 – Occidentalize

please post what you did as an answer so we can see what you did, and then accept it as an answer when you can. this helps us all learn. – Rossi

I used the following link #8013536 which says that you can use a dot '.' to return all instances of multiple tags with the same name & it worked fine. – Occidentalize

Answer 1

As Antonio indicated, I would use XQuery instead of Openxml. I've guessed at your source xml, and provided code for your 2 queries:

/* Please note I have used different cases than you to follow a standard (first letter capitialised for elements, lowercase for attributes.
   As xml is case sensitive, you may need to change the below code to suit the case of your data */

/* Bring your xml into an xml variable */

DECLARE @xml xml = '
<Records>
    <Record category="category1" editor="editor1" entered="2015-01-01" subcategory="subcategory1" uid="100001" updated="updated1">
        <Person ssn="ssn1" e-i="ei1">
            <Title>Title1</Title>
            <Position>Position1</Position>
            <Names>
                <First_name>FirstName1</First_name>
                <Last_name>LastName1</Last_name>
                <Aliases>
                    <Alias>test1</Alias>
                    <Alias>test2</Alias>
                    <Alias>test3</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
    <Record category="category2" editor="editor2" entered="2015-01-02" subcategory="subcategory2" uid="100002" updated="updated2">
        <Person ssn="ssn2" e-i="ei2">
            <Title>Title2</Title>
            <Position>Position2</Position>
            <Names>
                <First_name>FirstName2</First_name>
                <Last_name>LastName2</Last_name>
                <Aliases>
                    <Alias>test4</Alias>
                    <Alias>test5</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
</Records>'


/* The unary relationship of 1 element/attribute type per record in xpath */

SELECT T.rows.value('@category[1]', '[varchar](100)') AS Category
      ,T.rows.value('@editor[1]', '[varchar](100)') AS Editor
      ,T.rows.value('@entered[1]', '[Datetime]') AS Entered
      ,T.rows.value('@subcategory[1]', '[varchar](100)') AS Subcategory
      ,T.rows.value('@uid[1]', '[bigint]') AS [UID]
      ,T.rows.value('@updated[1]', '[varchar](100)') AS Updated
      ,T.rows.value('(Person/Names/First_name)[1]', '[varchar](100)') AS First_Name
      ,T.rows.value('(Person/Names/Last_name)[1]', '[varchar](100)') AS Last_Name
      ,T.rows.value('(Person/@ssn)[1]', '[varchar](100)') AS SSN
      ,T.rows.value('(Person/@e-i)[1]', '[varchar](100)') AS ei
      ,T.rows.value('(Person/Title)[1]', '[varchar](100)') AS Title
      ,T.rows.value('(Person/Position)[1]', '[varchar](100)') AS Position
FROM @xml.nodes('/Records/Record') T(rows)


/* Record to alias, one-to-many mapping */

SELECT T.rows.value('../../../../@category[1]', '[varchar](100)') AS Category
      ,T.rows.value('.', '[varchar](100)') AS Alias
FROM @xml.nodes('/Records/Record/Person/Names/Aliases/Alias') T(rows)

Pennsylvania

Answer 2

Try modifying the FROM clause as follows:

SELECT
...

FROM OPENXML(@hDoc, 'records/record/person/aliases')

Lupulin

Answer 3

When I work with XML files i do the following to split the nodes into columns:

XML file sitting in a column in a table with the following format:

<CustomData>
<Name>Shaun</Name>
<Surname>Johnson</Surname>
<Title>Mr</Title>
<Age>29</Age>
</CustomData>

The Select statement I use:

SELECT  
[ColumnName].value('(/CustomData//Name/node())[1]' , 'nvarchar(max)') AS Name,
[ColumnName].value('(/CustomData//Surname/node())[1]','nvarchar(max)') AS Surname,
[ColumnName].value('(/CustomData//Title/node())[1]' , 'nvarchar(max)') AS Title,
[ColumnName].value('(/CustomData//Age/node())[1]' , 'nvarchar(max)') AS Age
FROM TableName

Results:

Name  Surname  Title  Age
----  -------  -----  ---
Shaun Johnson  Mr     29

Regality

Answer 4

openxml must return a scalar value, you should use the .nodes method in XML joined with your OPENXML

Dight